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.70362
    26.46663
    28.64501
    27.3272
    26.63338
    26.51538
    26.82517
    28.43699
    27.23274
    27.73429

  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

© copyright 1999, S.E. Van Bramer
This page has been accessed 14479 times since 1/5 /96 .

Last Updated: 1/8/2004