Excel is cool!       

Lab Handout #3. See Attached Worksheet:
Free Fall. Reference: Data Table 2, Exp. 1 (Wilson).
[Page references from "Teach Yourself Excel 97 in 10 Minutes"(Fulton)]

A. Creating Column Headings.

1. Start Excel. A work sheet with cells will appear. Leave the first two
or three rows blank; you may want to create a heading with your name, section
and title later. Select A4 and A5. Then Click Format, click cells
(pages 93-4). The Format Cells dialog box appears. Click the Alignment tab.
Under Text Alignment, write center next to "Horizontal." Write center next
to "Vertical." Under Text Control, choose Merge Cells. Click OK.
Type "time(s)" in the cell merged from A4 and A5.
On the toolbar, click the center button to center the label. If the toolbars
are not visible, click View, click Toolbars, and then click Formatting
and click Standard.
2. Select B4 and B5. Then Click Format, click cells
(pages 93-4). The Format Cells dialog box appears. Click the Alignment tab.
Under Text Alignment, write center next to "Horizontal." Write center next
to "Vertical." Under Text Control, choose Merge Cells. Click OK.
Type "t^2" in the cell merged from B4 and B5.
On the toolbar, click the center button to center the label.
3. Select C4 to G4. Click the Merge and Center (page 94) button on the toolbar.
Type distance (m) in the newly merged cells C4 to G4.
4. Select C5. Type y1. Press enter. Select C5 again and click the
Center button on the toolbar. Select D5. Type y2. Press enter. Select D5
again and click the Center button on the toolbar. Repeat for E5 to G5.
5. Click H5 and type y(bar). Press enter. Select H5 again and click the center
button on the toolbar.
6. Click I5 and type d(bar). Press enter. Select I5 again and click the center
button on the toolbar.
7. Select cells A6 to A10. Then Click Format, click cells (pages 93-4).
The Format Cells dialog appears. Click the Number Tab.
Choose 2 decimal places. Click OK. Enter the five time values in the cells A6
to A10. Finally, select the cells A6 to A10 again and click the center
button on the tool bar.
Note: If you make a mistake, you can always click inside the cells and
make your corrections.
B. Processing data with formulas. See pp. 98-118.
8. In cell B6, type = A6^2. Press enter. Move the mouse pointer over the
fill handle of B6 and drag down the column over the next 4 cells into which you
want to copy the formulas. Release the mouse, and the squares of the data in
the cells in the first column appear in the second column. Select cells B6
to B10 again and then Click Format, click cells (pages 93-4). The Format
Cells dialog appears. Click the Number Tab. Choose 2 decimal places.
Click OK. Finally, select the cells B6 to B10 again and click the center
button on the tool bar.
9. Select cell C6 to C10. Click Format, click cells (pages 93-4). The Format
Cells dialog appears. Click the Number Tab. Choose one decimal place.
Click OK. In the cells C6 to C10, type in the value of y1. Then select
the cells again, and then Click Format, click cells (pages 93-4). The Format
Cells dialog appears. Click the Number Tab. Choose 1 decimal place.
Finally, select the cells again and click the center button on
the tool bar. Repeat these steps in an analogous fashion for the columns
under y2, y3, y4, and y5.
10. Click on cell H6, and type = AVERAGE(C6:G6). Press Enter. Now place
the mouse pointer on the fill handle of H6 and drag down the column over
the remaining four cells into which you want to copy the formula. Release
the mouse. The average of the times in the five columns should be inside the
cells of the column. Then select the cells again. Click Format, click cells
(pages 93-4). The Format Cells dialog appears. Click the Number Tab. Choose
two decimal places. Click OK. Finally, select the cells again and click
the center button on the tool bar.
11. Click on cell I6, and type = AVEDEV(C6:G6). Press Enter. Now place
the mouse pointer on the fill handle of I6 and drag down the column over
the remaining four cells into which you want to copy the formula. Release
the mouse. The average (mean) deviation of the times in the five
columns should be inside the cells of the column. Then select the
cells again. Click Format, click cells (pages 93-4). The Format Cells
dialog appears. Click the Number Tab. Choose two decimal places.
Click OK. Finally, select the cells again and click the center button on
the tool bar.
C. CREATION OF LINE CHART
Your work sheet should now look something like this.

Time(s)

t^2

Distance (m)

   

Y1

Y2

Y3

Y4

Y5

Y(bar)

d(Bar)

0.00

0.00

0.00

0.00

0.00

0.00

0.00

0.00

0.00

0.50

0.25

1.0

1.4

1.1

1.4

1.5

1.28

0.18

0.75

0.56

2.6

3.2

2.8

2.5

3.1

2.84

0.25

1.00

1.00

4.8

4.4

5.1

4.7

4.8

4.76

0.17

1.25

1.56

8.2

7.9

7.5

8.1

7.4

7.82

0.30

The following steps for chart creation present two methods: (I) An "elegant" but delicate approach requiring careful attention. It creates the chart from the above table as is. (II) An easier but slightly more cumbersome method that makes a chart by copying the relevant columns to another table first.

You can either method. Read both and decide which one you are most comfortable with. Method (I), however, allows you to learn a little more about the intricacies of Excel!

(I) "Elegant" Approach.
Y(bar) vs. t.

1. While holding down the control (Ctrl) key, select the column of time values under the time (s) heading. Continue to hold down the control key and select the column of values under the Y(bar) heading. 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…)
2. 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>.
3. The Chart Wizard Step 2 of 4 (Source Data)dialog box appears. This is where care may be required. A graph of your 5 data points should appear. It should represent a graph with only one set of data points, as a single parabolic-like shape. If it does not look correct, do the following. Click the Data Range Tab. On the data range line type = Sheet1!$A$6:$A$10,Sheet1!$H$6:$H$10. (Note: If you make a mistake, correct the line using only a mouse click to bring the cursor to the place where you want to make a correction, the backspace key, and the delete key. Do not use the arrow key.) After you type this, the Columns radio button should be checked under "Series in:". If it is not selected, check it now. Click Next>. You should now see the correct graph.
4. 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 "Y(bar)vs.t." On the Value(X) axis line, type t(s). On the Value(Y) axis line, type "Y(bar)." (b)Click the gridlines tab. Under Value (X) axis, select "Major gridlines." Under value (Y) axis, select "Major gridlines." Click NEXT>.
5. 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.
6. A graph should appear in the worksheet with your 5 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.
7. 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 "POLYNOMIAL." Under the Order option, choose 2 if it is not already chosen. Click the Options tab and put a check mark next to "display equation on chart." Click OK.
8. Your chart should display a best-fit parabola through your 5 points, and a trendline label with the equation of the parabola. 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 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. If you want, you can also edit the text of the label. Click the label once to select it. A rectangle appears around the label. Then click one more time again inside the label so that the cursor "blinks." Then edit the line. For example, you should change the x into a t.

y(bar) vs. t^2

9. While holding down the control (Ctrl) key, select the column of time squared values under the t^2 heading. Continue to hold down the control key and select the column of values under the Y(bar) heading. 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 (Source Data)dialog box appears. This is where care may be required. A graph of your 5 data points should appear. It should represent a graph with only one set of data points, as a single line. If it does not, do the following. Click the Data Range Tab. On the data range line type = Sheet1!$B$6:$B$10,Sheet1!$H$6:$H$10. Note that you are now typing in the letter B not A since we are dealing with the t^2 column! (Note: If you make a mistake, correct the line using only a mouse click to bring the cursor to the place where you want to make a correction, the backspace key, and the delete key. Do not use the arrow key.) After you type this, the Columns radio button should be checked under Series in. If it is not selected, check it now. Click Next>. You should now see the correct graph.
12. Repeat steps 4 - 8 to finish the chart creation for y(bar) vs. t^2.
Remember that when you right click a data point to create a trendline (step 7), choose "LINEAR", not "POLYNOMIAL"! Also, remember to re-label the x-axis as t^2 instead of t! (Step 4)

(II). "Separate Table" Approach. (A little easier.)

Y(bar) vs. t

1. Select the column of time values A4 to A10. See pages 49-53 about copying. Click Edit. Click Copy. Select another cell in the same column as the first set of time values, but lower in the worksheet. Click Paste. Excel inserts the contents of the set A4 to A10 (the numbers and the heading) into a new column beginning at the new insertion point. You might have to click outside the column to stop the "ant" trail encircling the original cells you selected.
2. This step is to copy the contents of the y(bar) column into a column next to the newly copied set of time values. However, you cannot simply copy and paste because the y(bar) column is created from formulas! You must use Copy and Paste Special! Select the column for y(bar) H5 to H10, including the heading. Click Edit. Click Copy. Select the cell just to the right of the time (s) heading of the column that you just copied and pasted. Make sure the y(bar) heading is aligned with the bottom half of the merged cells of the time(s) heading so that the y(bar) values will align properly next to the time values. Click Edit. Click Paste Special. The Paste Special dialog box appears. Under Paste, select Values. Under Operation, make sure None is selected. Then click OK. The y(bar) column should be correctly aligned next to the time column. Then select the cells again. Click Format, click cells (pages 93-4). The Format Cells dialog appears. Click the Number Tab. Choose two decimal places. Click OK. Finally, select the cells again and click the center button on the tool bar. Center the heading, if necessary. When you are finished with these steps, your new columns should appear as follows:

time(s)

 

Y(bar)

0.00

0.00

0.50

1.28

0.75

2.84

1.00

4.76

1.25

7.82

3. Select the two columns of numbers (excluding the column headings.)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.)
4. 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>.
5. The Chart Wizard Step 2 of 4 (Source Data)dialog box appears. A graph of your five data points should appear. Click Next>.
6. Repeat steps 4-8 under the "Elegant Method" (I) steps above.

Y(bar) vs. t^2.

7. This step is to copy the contents of the column of values of the time-squared. However, you cannot simply copy and paste because the time-squared column is created from formulas! You must use Copy and Paste Special! Select the column for time-squared B4 to B10, including the heading. Click Edit. Click Copy. Select another cell on the left side of the worksheet, but lower in the worksheet. Click Edit. Click Paste Special. The Paste Special dialog box appears. Under Paste, select Values. Under Operation, make sure None is selected. Then click OK. Then select the newly copied cells. Click Format, click cells (pages 93-4). The Format Cells dialog appears. Click the Number Tab. Choose two decimal places. Click OK. Finally, select the cells again and click the center button on the tool bar. Center the heading, if necessary.
8. This step is to copy the contents of the y(bar) column into a column next to the newly copied set of time-squared values. However, you cannot simply copy and paste because the y(bar) is created from formulas! You must use Copy and Paste Special! Select the column for y(bar) H5 to H10, including the heading. Click Edit. Click Copy. Select the cell just to the right of the t^2 heading of the column that you just copied and pasted special. Make sure the y(bar) heading is aligned with the bottom half of the merged cells of the t^2 heading so that the y(bar) values will align properly next to the time-squared values. Click Edit. Click Paste Special. The Paste Special dialog box appears. Under Paste, select Values. Under Operation, make sure None is selected. Then click OK. The y(bar) column should be correctly aligned next to the time-squared column. Then select the cells again. Click Format, click cells (pages 93-4). The Format Cells dialog appears. Click the Number Tab. Choose two decimal places. Click OK. Finally, select the cells again and click the center button on the tool bar. Center the heading, if necessary. When you are finished with the last two steps your new columns should appear as follows:

t^2

 

Y(bar)

0.00

0.00

0.25

1.28

0.56

2.84

1.00

4.76

1.56

7.82

9. Select the two columns of numbers (excluding the column headings.)
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 (Source Data)dialog box appears. A graph of your 5 data points should appear. Click Next>.
12. Repeat steps 4-8 in the "Elegant Method" steps above. Remember that when you right click a data point to create a trendline (step 7), choose "LINEAR", not "POLYNOMIAL"! Also, remember to re-label the x-axis as t^2 instead of t! (Step 4)

For any method that you use, 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.