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
|