# Using Excel for Statistical Calculations Homework Set

This problem set was developed by S.E. Van Bramer for Chemistry 366 at Widener University.

1. 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.7036 26.4666 28.645 27.3272 26.6334 26.5154 26.8252 28.437 27.2327 27.7343

2. 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.
1. Copy and paste the x y data pairs into Excel.
2. 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.
3. 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