Statistics Lab Exercise

This exercise will familiarize the student with the use of an Excel spread sheet and with the Method of Least Squares. You should be familiar with the basics of both Excel and LSQ before coming to the lab. The links above will get you started if necessary.

Double click on the desktop icon labeled"statisticslab.xls" or load "statisticslab.xls" from floppy disk. The file should open in Excel and should present a worksheet that should look something like the figure below.

The data in the first two columns represent data taken from a series of CCD observations of a star as it set in the west.
The entries are: Col.B=X(airmass), Col.C=measured value (star+dark+bias).

Your task is to determine the extinction in magnitudes per airmass by doing a Linear Least Squares fit to these data. Column headings have been provided to guide you.

Since you are going to use the Method of Least Squares to get the extinction in magnitudes per airmass, you will be fitting magnitude values as your dependent (y) variable to X (airmass) as your independent (x) variable.

  1. To get you started, enter the following into cell D4: "=C4-15-100" (leave off the quotation marks). A short cut is to enter "=" and then click on cell C4, then type "-15-100". This will calculate the value in cell D4 minus the average dark and minus the average bias.
  2. Now click on cell D4 and then drag the dragtag down over cells D5-D13. This expands the calculation of I over that range. The result will be I as a function of X.
  3. In cell B14 enter "sum(B4:B13)" ... the sum of the B column should appear.
  4. In cell B15 enter "B14/count(B4:B13)" ... the mean should appear. What do you think this formula does? What does the count(B4:B13) do? Why did we calculate the values in B14 and B15?
    You may need the sum and mean of your X values later and anyway you will need sums and/or means of most of the other columns you are going to calculate so this gave you the necessary starting point.
  5. Now enter a formula to calculate magnitudes from intensity in cell E4. Look at the result ... does it make sense? Note the suggested values I1=1 and m1=20.
  6. Now drag the column E formula down to row 13 and drag the sum and mean formulae across from column B to column E.
  7. Pause at this point and use Excel's graphing function to produce a plot of magnitude vs. airmass.
    (Highlight the X column, ctrlClick E3, shiftClick E13 and then select Insert Chart. Select Next>> and then X-Y Chart which is the 2nd item on the 2nd row.)
    Does it make sense? Perhaps it would help if you format the y axis vales to plot in reverse order. (In Excel,Double-click on the y axis of the plot and select "scale". StarOffice does not provide this function)
  8. At this point you are on your own. You know what is needed to do a least squares solution (if you dont, the refer to the Basic Statistics lesson). Add columns to calculate the various terms necessary to calculate the b (intercept) and m (slope) coefficients. The column headings of the provided spread sheet are there to provide a suggestion as to how your calculation should proceed.
  9. When you have the b and m coefficients, set up a column to calculate the values of y based on these coefficients.
  10. Calculate the O-C (observed - computed/calculated) magnitude values. Does the mean of this column make sense?.
  11. Enter the values you have calculated for: m, b, observed m(i), calculated m(i), O-C on the lab spread sheet above.
  12. Add the calculated y's to your graph.
  13. Fill in the equations used on the equation work sheet.
    Colum Label/Equation used:
    D3:
    E3:
    F3:
    G3:
    H3:
    I3:
    J3:
    K3: