Instrumental Analysis

Statistics Lab

Instructions

Review the following documents and Appendix 1 in the text before beginning these exercises.

Note: Use your browser's back button to return to this page.

A convenient way to do these exercises is to adjust the browser window to occupy one half of your computer screen and adjust the spreadsheet to occupy the other half of the computer screen. With the windows side-by-side you can read the instructions in the browser window and work in the spreadsheet window. At times you will need Excel in full screen. (You can also print these instructions if you prefer.) The data on the browser screen can be highlighted with the mouse and copied into Excel using Copy and Paste in the Edit menu.

Helpful hints:

The button makes the window full screen.
The button reduces the size of the window.
Adjust the window by dragging an edge or corner with the mouse.
ALT-Tab cycles between windows.
Save your file often!
Some frequently asked questions about Excel are at the bottom of this page.

Exercise A

If you are unfamiliar with spreadsheets work through whatever tutorial your spreadsheet program provides. (Look under the Help menu for help.)

Exercise 1

Title the top of the worksheet with "CHEM 4114 - Statistics Lab" and your name and date. A few lines down make a label for Exercise 1, and copy the following data (Data Set 1) onto the worksheet.

Data Set 1

Measurement  Voltage (v)
    1          0.453
    2          0.444
    3          0.457
    4          0.448
    5          0.451
    6          0.495
    7          0.447

Enter formulas to find the average and standard deviation of the data.

Click here to see an example screen.

Exercise 2

Make a label for Exercise 2 and plot Data Set 1. (If your spreadsheet program supports multiple worksheets, you can put the different exercises on separate sheets.)

Exercise 3

Make a label for Exercise 3. Copy the data and formulas from Exercise 1 to the new location for Exercise 3.

The sixth measurement in the data set is much higher than the other data. Show a Q-test calculation to determine if this data point can be rejected.

(Hint: the sixth data point can be discarded.) Delete the outlier and revise you formulas if necessary to recalculate the average and standard deviation. If you have to do extensive revision of your formulas, you may want to think about a more flexible way to write the formulas.

Exercise 4

Make a label for Exercise 4 and cut and paste the following data (Data Set 2) onto the worksheet.

Data Set 2

Concentration (ppb)	Analytical Signal
  0.00			  0.002
  5.00			  0.259
 10.00			  0.489
 25.00			  1.284
 50.00			  2.407
100.00			  4.903

Make a plot of Data Set 2. Do a linear regression to find the slope and intercept of the best line that fits this data. Report the standard deviations with these results.

You made the following measurements on an unknown:

0.333
0.349
0.341

Report the mean and standard deviation of this result.

Final Exercise

Print your worksheets, including the formulas, and tape them into your lab book. Save the worksheet to disk so you can use the formulas in calculations for your later labs.

FAQs about Excel

Q. Can I use the built-in Data Analysis functions in Excel (under the Tools menu)?
A. Yes, but if you use the summary output table of results you must know what it gives you and how to convert to what is requested in the lab handout. Note that the Data Analysis tools are only available if you have the full installation of Excel.

Q. How can I print the formulas?
A. Open Options in the Tools menu and click on the View tab. Select Formulas as a Window Option.

Q. My data is displayed equally spaced along the x-axis instead of according to the x-data values.
A. When you insert the chart use the "XY (scatter)" chart type.

Q. My spreadsheet doesn't have multiple worksheets, how should I do the statistics exercises?
A. You can put all of the exercises on one sheet, or you can put the different exercises in separate files. If you put everything on one sheet space it so that the pages print well.