Using Excel for Statistical Calculations Homework Set
This problem set was developed by S.E. Van Bramer for Chemistry 366 at Widener University.
- Calculate the average, standard deviation, and 95% confidence interval for the dataset below. Calculate these values using your calculator, using the data analysis tools in Excel, and using the built in statistical functions in Excel. Compare your results and use the help information in Excel to try and explain any differences in your results.
27.70362 |
26.46663 |
28.64501 |
27.3272 |
26.63338 |
26.51538 |
26.82517 |
28.43699 |
27.23274 |
27.73429 |
- The following dataset is the NIST Norris dataset for certifying linear least squares regression results. The certified values for this dataset are: y = 1.00211681802045*x - 0.262323073774029. The dataset and the certified results are given below.
- Copy and paste the x y data pairs into Excel.
- Determine the slope and intercept using the data analysis tools in Excel, the built in statistical functions in Excell, the LINEST function in Excel, the TREND function in Excel, the SLOPE function in Excel, the INTERCEPT function in Excel, the trendline tool in the Excel graphing, and using the formulas in the Statistics Handout.
- Calculate the predicted x value and the uncertainty in the predicted x value for y = 200.0. Use the TREND function in Excel and the formulas in the Statistics Handout.
y | x |
0.1 | 0.2 |
338.8 | 337.4 |
118.1 | 118.2 |
888.0 | 884.6 |
9.2 | 10.1 |
228.1 | 226.5 |
668.5 | 666.3 |
998.5 | 996.3 |
449.1 | 448.6 |
778.9 | 777.0 |
559.2 | 558.2 |
0.3 | 0.4 |
0.1 | 0.6 |
778.1 | 775.5 |
668.8 | 666.9 |
339.3 | 338.0 |
448.9 | 447.5 |
10.8 | 11.6 |
557.7 | 556.0 |
228.3 | 228.1 |
998.0 | 995.8 |
888.8 | 887.6 |
119.6 | 120.2 |
0.3 | 0.3 |
0.6 | 0.3 |
557.6 | 556.8 |
339.3 | 339.1 |
888.0 | 887.2 |
998.5 | 999.0 |
778.9 | 779.0 |
10.2 | 11.1 |
117.6 | 118.3 |
228.9 | 229.2 |
668.4 | 669.1 |
449.2 | 448.9 |
0.2 | 0.5 |
From NIST Certified Values
Certified Regression Statistics |
|
Parameter |
Estimate |
Standard Deviation
of Estimate |
Intercept
|
-0.262323073774029
|
0.232818234301152
|
Slope
|
1.00211681802045
|
0.429796848199937E-03
|
|
Residual Standard Deviation |
0.884796396144373
|
|
|
R-Squared |
0.999993745883712
|
|
|
|
|
Certified Analysis of Variance Table |
|
Source of Variation |
Degrees of Freedom |
Sums of Squares |
Mean Squares |
F Statistic |
|
Regression |
1
|
4255954.13232369
|
4255954.13232369
|
5436385.54079785
|
Residual |
34
|
26.6173985294224
|
0.782864662630069
|
|
Please send comments or suggestions to svanbram@science.widener.edu
Scott Van Bramer
Department of Chemistry
Widener University
Chester, PA 19013
© copyright 1999, S.E. Van Bramer
This page has been accessed
times since 1/5 /96 .
Last Updated: 1/8/2004