Excel is cool!

Lab Handout #2. See Attached Worksheet:  Best Fit Line.

A. USING FORMULAS TO PROCESS DATA.

1. Start Excel. A work sheet with cells will appear. Leave the first two or three rows blank. In the first column enter the integers xi = 25, 44,78, 80, 43, 58, 95, 67.
2. In the second column, enter the integers yi : 12, 28, 47, 70, 16, 53, 72, 38.
[3. On the third column, enter = A4^2 in the highest cell for
your column. (i) Move the mouse point over the fill handle. (ii) Drag the fill handle down the column across the next 7 cells into which you want to copy the formula. Each cell should contain the square of the integer in the corresponding cell in the first column.]
[4. On the fourth column, enter = A4*B4. i) Move the mouse point over the fill handle. (ii) Drag the fill handle down the column across the next 7 cells into  which you want to copy the formula. Each cell should contain the product of the integers in the corresponding cells in the first and second columns.]
[5. Click inside the empty cell at the bottom of the first column. (i) Type =Sum(A4:A11). Press Enter. The sum of the integers in the first column should appear in this cell.]
[6. Click inside the empty cell at the bottom of the second column. (i) Click the equal (=) on the left end of the formula tool bar. Click the
functions button (a downward arrow) and select "SUM" from the list.
(ii) A pop-up formula palette appears with two command lines. The
top command line should contain B4:B11. Leave the second command line blank.  Click OK. The sum of the integers in the second column should appear in this cell.] (Note: If you cannot see the formula tool bar, click View and click Formula Bar to make it visible.)
[7. Click inside the empty cell at the bottom of the third column. (i) Type =Sum(C4:C11). Press Enter. The sum of the integers in the third column should appear in this cell.]
[8. Click inside the empty cell at the bottom of the fourth column. (i) Click the equal (=) on the left end of the formula tool bar. Click the
functions button (a downward arrow) and select "SUM" from the list.
(ii) A pop-up formula palette appears with two command lines. The
top command line should contain D4:D11. Leave the second command line blank. Click OK. The sum of the integers in the fourth column should appear in this cell.]

B. CREATION OF LINE CHART

Your worksheet columns should appear as below:

25 12 625 300
44 28 1936 1232
78 47 6084 3666
80 70 6400 5600
43 16 1849 688
58 53 3364 3074
95 72 9025 6840
67 38 4489 2546
490 336 33772 23946

9. Select the first two columns above (excluding the cells with the sums)by holding down the mouse and dragging from the upper left hand corner to the lower right hand corner of the array of cells. Click the Chart Wizard icon on the tool bar ---three, small vertical bars----blue, yellow red. (Note: If you cannot see the Chart Wizard icon, click View, click Toolbars, then click Standard. You can also start the Chart Wizard by clicking Insert and then clicking Chart…)
10. The Chart Wizard Step 1 of 4 dialog box appears. Under the Chart Type window select "XY (Scatter)." In the Chart sub-type window select "Scatter. Compares pairs of values." Click NEXT>.
11. The Chart Wizard Step 2 of 4 dialog box appears. A graph of your 8 data points should appear. Click NEXT>.
12. The Chart Wizard Step 3 of 4 dialog box appears.
(a) Click the Titles tab is if is not already selected. On the Title line, type "Best Fit Line." On the Value(X) axis line, type X.
On the Value(Y) axis line, type Y. (b)Click the gridlines tab. Under Value (X) axis, select "Major gridlines." Under value (Y) axis, select "Major gridlines." Click NEXT>.
13.The Chart Wizard Step 4 of 4 dialog box appears. Under "Place chart," select "as object in sheet 1." (Sheet 1 is the default work sheet you opened in Excel.) Click FINISH.
14. A graph should appear in the worksheet with your 8 data points. Note: You can click inside the chart and drag it to a more convenient position on the work sheet if necessary. Note: If you make a mistake on these options after the chart is created, you can edit by right clicking the chart. Then on the pop up menu, click Chart Type or Chart Options to make the changes.
15. Place the mouse pointer on a data point and RIGHT CLICK the point. A pop up menu appears. Click "Add trend line." The Add Trendline dialog box appears. Click the Type tab if it is not already selected and select "LINEAR." Click the Options tab and put a check mark next to "display equation on chart." Click OK.
16. Your chart should display a best-fit line through your 8 points, and a trendline label with the line equation. To move the label, select it and drag it to a more convenient location.(It is also helpful to select the series 1 legend next to your line graph and press the delete key to remove this legend from your chart. It takes up needless space.) Note: You can modify the trendline. Just double click the line and make the changes you want in the Format Trendline dialog box. You can also change the trendline label. Just double click the equation and change the font, or change the way the numbers are displayed.
17. Save your worksheet on a floppy disk. To print out, select the cells covering the rows of data and the chart. Select Print under  the file menu and check "print selection"; A print out of the page with everything mentioned should result