Transcript Using Excel for Computer Labs

General Astronomy
Using Excel for Lab Analysis
Introduction
• Being able to use a spreadsheet to help in
analysis of any laboratory work is a very
useful skill.
• You can use it to tabulate your data,
perform (repetitive) computations and do
a variety of data analysis procedures
– These include, but are not limited to
• Graphics
• Statistics
• Correlations
Introduction
• These notes will lead you through the
process of setting up a spreadsheet to
analyze one of the computer labs assigned
for this class.
• We will assume that the lab has been
done and the data is now available
• We will be using the Microsoft Excel©
spreadsheet, but most spreadsheet
programs will operating in a similar
fashion.
Starting up…
Start your spreadsheet program by
double-clicking its icon
The program should appear looking something like this:
Let's start by clicking the cell labeled by the intersection of column 'A'
and row '1': Then typing a heading
Next add a set of labels for the table. It should look like the table
shown in the lab:
Notice that we've boldfaced the header; used 'symbol' format to get
the Greek letters, and put some boxes about the headings
•
To insert a symbol, click the Symbol on the Ribbon (top portion). A box will appear
and you can click the symbol you want. Also we need some constants, so we will
insert 4 blank rows to add things like the speed of light, c, and the laboratory
wavelengths:
Now, type in the values obtained during the lab
Adding formulas
Now, we have to add the formulas to do all the calculations. We will
only use the 1st row. Once we get that one right we can copy (or
using spreadsheet-speak: replicate) to the other rows.
A formula starts with an '=' sign in the cell. You use a combination of
typing and the mouse to build it – generally the keyboard to type
operations such as multiply (*) and the mouse to select other cells
in the formula.
For example in cell H10, we will calculate the difference between the
measured value, in cell F10 and the lab value in cell C4.
Click cell H10, type a '=' then click cell F10, type a '-' then click cell
C4
You should see the following in the white area above the column labels:
=F10-C4
[Note: we are making a mistake here. The formula itself is OK. However there is a slight subtlety
that can cause problems later, but at this point we want to do that to show what will happen.]
You can see the result, 88.3 in the cell.
Now do the same thing for cell I10.
It should look like: =G10-C5
Now, use the mouse to select both H10 and I10, then use
the 'copy' (or Control-C).
Then, click on cell H11, and 'paste' (Control-V)
Look at H11 and I11. Something is very wrong!
If you select the H11, the formula says: =F11-C5
That's incorrect. That is λK(measured) - λH(Lab)
It should be, =F11-C4
Using the '$' operator
The problem was caused during the copy-paste. Spreadsheets
are setup to automatically adjust the rows and columns When we pasted into H11 from H10 all rows in the formula
were incremented by 1.
We need to tell the program that it should not change the C4 to a
C5.
This is done with the '$' mark. Placing this before a row value
(or a column value) freezes the value. So the formulas in H10
and I10 should look like:
=F10-C$4
=G10-C$5
Then when we copy-paste the values in H11 and I11 will look like
=F11-C$4
=G11-C$4
In fact, now we can copy H10 and G10 and using the mouse, select H11
through H14 and paste.
Now, that looks good - and we did the arithmetic for all the rows of
data with a couple of keystrokes
Now we can write the formula for VK. Notice that we freeze both the
column and the row.
Next we'll select J10 and copy; select K10 and paste. Then select both
J10 and K10 and paste into J11 through J14
That was easy. Notice the formula in the selected cell, K14, has automatically
incremented the needed cells, but kept the reference to the speed of light
in cell C3.
Now, select L10. The formula is =AVERAGE(J10:K10).
Then select it and copy-paste (replicate) it down the rows
A really easy way to do repetitive calculations.
Now the hard one, the formula for Distance will be: (in D10)
=10^((C10-B10+5)/5)
Or, "10 to the ((m-M+5)/5"
The formula in E10 is simply:
=D10/1000000
All of the basic arithmetic has now been done. (With a bit of formatting to make the
decimal places look OK).
Now we can add the analysis portion.
We need to save some room for the images, so let's hide a few rows.
Use the mouse to 'paint' rows 2 through 6; Then right-click the mouse.
A popup panel will appear. Select 'hide'.
Notice the rows "jump" from 1 to 7. You can bring them back by doing the same
selection, but choosing 'unhide' from the popup
On the ribbon (the top portion of the screen), select
Insert, then Scatter, then click the image with points,
but no lines.
Right-clicking the chart, then selecting 'Select Data' followed by 'Add' brings
up an Edit Series popup. Use the mouse to enter data into the x and y
boxes. Then hit OK for this Edit Series and for the Select Data boxes.
Then double-click the chart, and – on the ribbon – click 'layout 3'. You may have
to again select Change Chart Type and select the points only image.
Right-clicking the Trendline and then selecting Format Trendline brings
up a popup. Select 'Set Intercept = 0.0' and 'Display Equation…'
The slope of the trendline, in this case 68.79, is the Hubble Constant, H
We can copy that to a convenient column
Now we can just add the remaining computations.
Using the fact that 1 pc = 3.09 x 1013 Km
Formulas:
Cell K19:
Cell I20:
Cell I21:
Cell K21:
=I19*I18*1000000
=I19*I17
=K19/I20/31500000
=I21/1E9
EASY!