MA 117 Laboratory Project 1 Sample sheet Names: Does the system of three equatio unknowns given by x+y=5 x - y = -3 x - 4y = -8 have a simultaneous solution? J your answer: The point(s) of intersection are at approximately: Using GoalSeek, we found that th intersection point(s) are at: Names: Does the system of three equations in two unknowns given by x+y=5 y = -3 4y = -8 have a simultaneous solution? Justify your answer: The point(s) of intersection are at approximately: Using GoalSeek, we found that the intersection point(s) are at: x y 0,1 0,2 1 1,5 1,7 1,8 2 2,1 2,2 2,9 Names: 2,07 2,14 2,63 2,8 3,1 2,95 3,4 3 3,4 3,7 1. What is the equation of your hand-drawn line of best fit? 2. What is the equation Excel's Trendline feature gives for the line of best fit? MA 117 Laboratory Project 1 Objectives of laboratory: • Learn how to plot lines in Excel and use the plots to find approximate intersection points • Learn how to find nearly exact points of intersection in Excel • Learn how to plot points in Excel • Learn the concept of “line of best fit”, and how to add one exactly in Excel In general, in the lab, you will work in pairs; if there are not an even number of students in class, there may be one group of 3. Each group submits just one lab project, with names of all who participated in that group’s work on it. When it says to type, or open, something which is in quotation marks, it mean to type only what’s in the quotation marks, not the quotation marks themselves. The quotation marks are just being used to tell you where to begin and end typing. Open Excel; from the File menu, choose Open; choose “Users on Mathserv…”, then “MA 117”, then “Lab1.xls”. Begin with the Sample sheet. (The names of the sheets are on tabs at the bottoms of the sheets.) Introduction. 1. Inputting an independent variable in Excel. In box A2, type “t”. In cell A3, type “10”, in cell A4 type “-9”, and then highlight cells A3 and A4. There should be a small black square at the bottom right corner. Move your cursor over to that cell; as you move the cursor over the small square, the cursor should change from a white “+” to a black one. Excel calls that the “Fill Handle”. Hold down your right mouse button and drag down the A column; you should see little numbers appearing in a yellow box below and to the right of the cursor. Release the mouse button when the number gets to 10. This should place the numbers –10 through 10 in column A. Next, highlight cells A3 through A23, go to the top of the spreadsheet, and choose “Insert”, then “Name”, “Define”, and in the box, click on the “t” which is there, then OK. Now cells A3 through A23 are known as t by Excel. When you type a formula which uses t, Excel will look at the value in the corresponding row of column A. (t was used here because once you’ve used a name anywhere in an Excel workbook you can’t use it again for something else. You will use x as the variable name on the part you do to hand in.) 2. Inputting a formula in Excel. Excel is happy to work with formulas, but they must be solved for the dependent variable. To plot a line, you will need to first find points on that line, and then plot the pairs of points. For example, to plot the line 2t + y = 6, you first solve it for y: y = 6 – 2t. Next, in a column other than the column you’ve names t, you put in its formula: in this case, type, in B3, “= 6 – 2*t”. Notice that we inserted an asterisk to denote multiplication: in algebra we indicate multiplication by simply putting two things next to each other: 2t. But for almost all software which does mathematics, you must put in an asterisk to represent multiplication: 2*t. Press Enter; you should see the value 26 appear in cell B3, since A3 has -10 in it, and 6 – 2*(-10) = 26. Next, click on cell B3 again, and move the cursor over to the fill handle. Drag the fill handle down to cell B23. All these cells should become filled in with values for y which are on the line 2t + y = 6. Follow this same procedure to put the values for the line t – y = 8 in cells C3 through C23. 3. Graphing lines or curves in Excel. We will now graph the two lines you filled in above. Select cells A3 through C23. Click on the picture of the bar chart at the top of the Excel spreadsheet. This brings up the Chart Wizard. Click on “XY (Scatter)”, and then on the sub-type which shows curved lines. Click on “Next”. Click on the Series tab, and name Series 1 either “2t + y = 6” or “6 – 2t”. Name Series 2 with its equation. Click on “Next”. If you wish (in later labs, you will need to do this, when the variables have a meaning from the context), give names to your independent (Value (X) axis) and dependent (Value (Y) axis) variables and the chart. Then click on Finish. The graph should now appear on the spreadsheet. You can move it if it covers your other data. If you want to see a particular part of the graph better, or if you want the actual values to be the whole of the graph, you can move your cursor over to one of the axes – do this first with the x-axis - and right-click on it. Choose Format Axis. Click on the Scale tab. Let’s say we want to see the interval where the two lines cross better. They appear to cross near (5,-3). So we want to zoom near that point. For the minimum, type 2, for a maximum, type 8, and for major unit type 1. Click OK. Next, format the y axis so that you zoom in also. This should find you a pretty good approximation for the intersection point. 4. Finding intersections in Excel. There’s a more accurate way to find intersections in Excel; it’s a tool called “Goal Seek.” Goal Seek can’t set one equation equal to another, but it can find where a single formula is equal to a given value. If we want to know when A(x) = B(x), we can just as well find out when A(x) – B(x) = 0. So label column D (in cell D2) “Line 1 = Line 2”, and in cell E3 type “=B3-C3”. To find the intersection of the two lines, click on cell D3, and go to Tools menu at the top of the sheet. Choose Goal Seek. A window should open saying “Set Cell D3” “To Value (blank)” “By Changing Cell (blank)”. You want to set Cell D3 to ”0” because then the two y-values will be equal; and you want to do this by changing t, which is cell “A3”. Click OK. Once you have a value, take the x-value from A3, the y-value from B3 (or C3: they should be equal), and this should be your intersection point. It should agree with the approximate value you found graphically. Part I: Finding Intersections of Lines 5. Now it’s your turn. Click on the bottom tab “Part I.” Your job is to find out whether the equations x + y = 5, x - y = -3, and x - 4y = -8 have a single simultaneous solution. Do this by first putting an independent variable, and naming it x, in column A, putting the first equation’s values in column B, the second equation’s values in column C, and the third equation’s values in column D (as we did on the Sample sheet for the two earlier equations). Graph the equations, and move the graph so it’s below all of the data. Is there a simultaneous solution to all three equations? Answer this and explain your answer in the first text box. Find approximate values for the intersection point(s), and enter these in the next text box. Then, if there is a single simultaneous solution, use column E to find it. If not, find each intersection between pairs of lines by using columns E, F, and G and using GoalSeek. Enter these point(s) in the final text box (and be sure to put your names in the Names box). Print out this sheet before going on to part II, but be sure to use Print Preview to make sure that your graphs or text boxes don’t overlap between two pages. Part II: Lines of Best Fit 6. Move to the sheet labeled Part II. Highlight these cells and do the chart wizard, this time choosing “XY (Scatter)” but keeping the picture with individual separated points. Once you have the chart on your worksheet, print out just the chart, and draw on a line which appears to best fit these points: that is, a line which has some of the points above it, some below it, and not all the early points below and all the later points above, but the points are scattered about the line throughout. Further, the distances of the points from the line should be as small as possible, overall. (The latter is the official definition, roughly.) From the line you drew in, find the approximate slope and then its equation. Write this equation in answer to question 1 in the text box. 7. Now go to the Chart menu at the top of the worksheet, choose “Add Trendline”, click on the “Options” tab, click in the box next to “Display Equation on Chart”. Click “OK.” This is the Line of Best Fit. Write its equation in answer to question 2 in the text box. Print out this sheet (after you put your names in the Names box) and hand in the printouts from both Part I and Part II.
Purchase answer to see full attachment