Experimental uncertainty (error) in replicate measurements using Excel

It is common (and good) practice to obtain repeated (replicate) measurements of the same experimental value, e.g. measurements of gas pressure (in pascals):

p / Pa

5246

5471

5821

5321

5537

5329

This data is analysed using:

Equations and functions to calculate - see video Replicate Measurements A

  • Mean value, x, (p171) using function AVERAGE(data)
  • Sample standard deviation, s, (p173) using function STDEV(data)
  • Sample size, n, using function COUNT(data)
  • Standard uncertainty, u, (p221) using the equation, u = s/√n
  • Degrees of freedom, df, (p222) using the equation, df = n - 1
  • t-value (p222) for 95% confidence using the function = TINV(0.05,df)
  • 95% Confidence deviation, Cd, (p221) using the equation, Cd  = t*s/√n
  • 95% Confidence interval (p221), minimum and maximum values, using CI = x ± Cd
    (NB Do NOT use the function CONFIDENCE for experimental sample data)

(The page numbers given in brackets above refer to the text: Essential Mathematics and Statistics for Science, 2nd Ed, by G Currell and A A Dowman)

The Descriptive Statistics option in Data Analysis tools to - see video Replicate Measurements B

  • Calculate the same values as obtained by the equations and functions above

Excel demonstration of the effect of random experimental variations - see video Replicate Measurements C