Excel Primer

Numerical Analysis of Lab Data

NOTE THAT THIS PRIMER ASSUMES THAT THE READER HAS NEVER USED A SPREADSHEET AND IS INTENDED TO GUIDE THE NOVICE THROUGH SOME SIMPLE ANALYSIS. NEW FUNCTIONS ARE SHOWN IN enlarged type IF YOU NEED TO FIND THEM AGAIN.


The task at hand involves organizing and analyzing your data to determine whatever conclusions may be lurking among them. A useful tool for this work is a spread sheet; we will use a product called Excel. When you boot up Excel, you will see a collection of cells into which you can place information, one piece of data per cell. Left to that application, all we would have is a simple table of data.

A spread sheet allows you to tie cells together in ways that are useful to you and can save you considerable drudgery along the way. Let's see what we can do with the data we collected. Go to Excel. You will notice the columns are lettered and the rows are numbered. We can give each cell a unique letter-number address, called an ordered pair, by-the-by. The cell in the upper left-hand corner is A1. To the right of that cell is B1; below B1 is B2 and so forth.

The default setting for column width is nine spaces. If you wish to make a column wider, column A for example, put the cursor between the A and the B and it turns into a black, skinny cross when it gets to the boundary between column A and B. By putting the black cross on the line separating A and B, you can make column A wider to accommodate longer entries or narrower, maybe to squeeze more columns to a page or as necessary.


It is likely that , when ypu print yoir document, yoi may wish to
print grid lines. Pull down "File" menu, click "page set-up", click "sheet"; select "print gridlines"; click "okay".
More often than not, spreadsheets are displayed with the print rotated 90 on the page. This is called the
landscape view. To set this up, pull down the “File” window, Click “Page set up”, click on the “paper size” tab, click “landscape”, click “ok”.

 

Basic Operations

A) enter "7.3" in cell A1; enter "6.4" in cell A2. Notice that every entry in any cell shows up on the task line above the spread sheet. You can edit the contents of any cell from this task line or from rhe cell itself.
B) Let's
add these numbers and put the sum in some arbitrary place like A3. Click on cell A3 and type = (equal sign) folllowed by A1 + A2. Click enter. Note that adding these two cells fits easily in cell A3. If we were adding more cells, or if the formula to be executed were longer, Excel would fit everything in one cell.
C) Try
subtracting,, multiplying, and dividing A1 and A2 with results appearing in A4, A5, A6 respectively.
D) It would have been nice to label columns A3 A4, A5, A6 add , subt, mult and div respectively, but we forgot to so so. No problemo. Put the cursor in any box on line 1, pull down the "insert" window, click "rows" and a new row will appear. Label the columns "add , subt, mult and div" as appropriate.
E) Notice that A5 & A6 have digits for nine places beyond the decimal point
. Click on A6 and. then find the decimal reduce button on the tool bar. Click it until you have only an appropriate number of decimal places.
F) What if the numbers had been in
scientific notation? Go back to cell A2 and change the entry to 7.3 ^4. Go to B2 and change the entry to 6.4 ^-3. Note that changes in either column are updated automatically in the final sum, difference, etc .
G)
The pull down trick. We wish to test addition with other pairs. We could enter random numbers in cells A3, A4, & A5 etc and cells B3, B4, & B5 and then visit cells C3, C4, & C5 and cause A3 + B3 = C3 In cell A3, type in "= A2 + 2". The entry in A3 should be two greater than the answer in A2. Cell A3 has a black nodule in the lower right-hand corner. Place the cursor over this nodule and the standard white cross turns inti a skinny black cross.
add sub mul div
8.5 3.7 12.2 4.80 31.45 2.30
10.5 1.7 12.2 8.80
11.5 -0.3 11.2 11.80
12.5 -2.3 10.2 14.80
13.5 -4.3 9.2 17.80
14.5 -6.3 8.2 20.80
15.5 -8.3 7.2 23.80
16.5 -10.3 6.2 26.80
17.5 -12.3 5.2 29.80
18.5 -14.3 4.2 32.80
19.5 -16.3 3.2 35.80
20.5 -18.3 2.2 38.80
21.5 -20.3 1.2 41.80
22.5 -22.3 0.2 44.80

WHAT FOLLOWS ARE DIRECTIONS FOR USING EXCEL FOR THE INCLINED PLANE LAB.
You may have measured distances in centimeters which is not bad in itself. For work later on, you should convert these distances to meters.
1. In cell A1 type “t for .20 m” (do not type the “ “marks)
2 Below A1 type in the data in any order, one datum per cell.
3. Repeat steps 1 & 2 for the other sets of data points.
4. It would be nice to know the
average time for each column. Here are two methods:
a) a direct method -- In the first empty cell at thne bottomof a column,

In the cell below the last datum in column A (cell A12?) type “=“. This tells the software to dust off the calculator. Find an click the function wizard at the top of the page. In the new box that comes up, click “statistical” in the left column and “average” in the right column. Click “next”.
5. You will be asked for a range of numbers. You may highlight them with the cursor or type in “A2:A11”. Click “finish” .
6. Part of the formula will appear in cell A12. (All of the formula will always appear in the bar above the sheet.). Press the enter key and the average for the column will appear.
7. The average will have several decimal places. We need only two. Click on A12 and then find the
decimal reduce button on the tool bar. Click it until you have only two decimal places.

8.
Copy math operation (the pull down trick). We would like the average for the other columns, but steps 4-7 are pretty tedious. Click on A12 and notice the black dot in the lower right corner. Put the cursor on the dot and it turns into a thin black cross. Hold down the left mouse button and drag the cross through B12, C12, etc. under each column. When you release the button, all the column will be averaged.


9. It would have been nice to label the 12th row as average times, but we didn’t plan ahead. Not to worry. Go to the first column and click on the “A” at the top. The column turns dark. Pull down the insert window and click “column”. A
new column will be added to the left of what was highlighted and all the columns will be relettered.


10. Go to the new A12 cell and type “avg. time (sec)”
11. You will have another whole set of data to deal with from the second trial with the new angle. It would be nice to keep these data on a separate page. At the bottom of the spreadsheet are some index-like tabs that say sheet 1, sheet 2, etc.
Clicking on a tab gives you a new sheet. Double click on a tab and a box appears with a prompt to name the sheet. Name sheet 1 something appropriate and likewise sheet 2. You can now keep the data separated. Do not add data to sheet 2 yet.
12. Find a convenient space, perhaps columns I & J are free. Type “distance” in cell I1 and “avg. time” in cell J1. In each cell in the I column, enter the distances down the ramp. In the cells of the J column, enter the corresponding average times. It will be useful later if these data are displayed from small distance to large distance, top to bottom. You could
sort them when you enter them the first time. But that may prove tedious if you had thousands of data points. Try entering the distances in the I column in some random order. Be sure to put the corresponding average time in the corresponding J cell. Now highlight all the data in both columns. Pull down the “data” window. Click “sort”. You will have a choice of which column will drive the sort. Choose “I”. You will have a choice of or”ascending” or “descending”. Click “ascending” to arrange the numbers in ascending order as you progress down the column. Neat, huh?
13 Direction 6 on the lab sheet says to calculate distance divided by time. In cell K1 type “dist/time”. In cell K2 type “=I2/J2”. Hit enter and the calculation is done. Use the pull down trick from #8 above and the rest of the K column will follow.
14. Notice the
faint dotted line after the M column. That’s the right side of the page. We have a little space left. Label cell L1 “2xdist/time^2”. We are now ready for direction 7 on the lab sheet. In cell L2 enter “=2*I2/J2/J2”. Hit enter and do the pull down trick.
15. Direction 8 on the lab says to
calculate the angle of elevation. Find a small convenient space, say cell A15. Type”rise=”. In A16, type “run=”, In A17 type “tan angle=”. In A18 type “angle=”. In A19 type "sin angle=". Now in cell B15 enter your rise value; in B16m your run value. In cell B17 type “=B15/B16“ and hit “enter” Now go to cell B18 and enter ”=“ and go to function wizard button on the tool bar. Choose “math and trig”; in the right column, choose “ATAN” in the right column and enterB17. hit “enter” and B18 shows the angle in radians. To show the angle in degrees, go to B18 and change it to “=ATANB17*180/PI” YOU WILL FIND pi ON THE FUNCTION WIZARD OR YOU CAN USE 3.14 INSTEAD. Hit enter. B18 shows the angle in degrees. Can you execute the right calculation to show sin angle in B19?
16. Direction #8 suggests that you calculate gsin(angle). In cell A20, type "gsin(angle)". In cell B20 type "=9.8*B19. hit enter. How does the value in A20 compare to the values in the L column?

17. We would like to graph some of our data, specifically distance vs time. Find a convenient column for the time data and in the top cell of that new column type "=J1" Hit enter. The contents of cell J1 will appear and will be surrounded by a dark border with a dark spot in the lower right corner. Pull down trick! The times are now there. Repeat the procedure for distance and place the distance values in the column to the right of the time column.
18. Highlight the data in the two new columns you have just created. Go to the tool bar and click Chart wizard. the "ghost" of a chart will appear near the cursor. Move the ghost over the highlighted cells and click the mouse. You will now go through a series of five query boxes. The first will ask if the highlighted data are those that you wish to graph. Click next. The second will ask which chart you would like. Click X-Y scatter and next. Third you will face six choices. Click 6. The fourth tells you that the first column (time in our case) will be plotted on the horizintal axis. It also says the first row (your column labels) will be the legend for your axes. Hit next. The fifth box will let you title your graph. Hit finish and the graph appears on your sheet. Click inside the box surrounding the graph and you can move it where you want it. Click outside the box and small markers appear around the box. Put the cursor around the box and a black double arrow appears. Move the double arrow to re-size the box.
19.Repeat steps 17 & 18 to graph distance vs. time^2.


Last edited 01/01/06

Return to lab directory