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 |
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 didnt 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 orascending 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. Thats
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. Typerise=.
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