error in average

Question

Hello,

This is probably more of a statistics question than an excel one, but thought someone here may be able to help.

How does one go about propagating error through an average of values that each have their own error. For example, I want to average values in A1:A3 with their corresponding error values in B1:B3. Average value is in A4, and want the error of average in B4, as in:

    A         B

1  4.35    2.364

2  4.26    1.589

3  4.41    0.003

4  4.34     ?

Is their a separate error averaging formula in excel, or can someone point me in the direction for the formula for this type of error propagation?

Thanks for any help,

-wox

Answer 1

Wox33,

On one hand it sounds like you want to calculate the mean error in the mean, which would be derived from the sample standard deviation divided by the square root of (sample size - 1), and doesn't make use of any known errors, just the original data.

In your example data, the standard deviation of the data points in column A is 0.0616, so the mean error from the mean would be 0.0616 / sqrt(2) = 0.0436, and hence the mean = 4.34 + or - 0.0436

On the other hand, however, like joeu2004 I wonder about the error values you have given in column B. How are the error values in your 'real' data set actually known in practice? What does it mean to say they are 'error propagated values'?

_______________________________________________

Regards, Tom

Answer 2

Thank you for the reply,

The error values in B are not standard deviations, but error propagated values for the data points in A. Values in A are not averages either, but independent replicates from an experiment. Data here is made up, and used to make it obvious that the error on the average is not the standard deviation. At the moment I am assuming that the error for the average values would simply be the SQRT(SUMSQ(B1:B3)). Assuming is never a good idea though.

Answer 3

Wox wrote:

> How does one go about propagating error through an average

> of values that each have their own error.

Tell us more about the "error" values in column B.  How were they determined?

For example, is column A an average of observations or measurements, and column B is the std dev or avg dev?  If so, which?

Also, if the values in column A are averages, then averaging those values is not valid mathematically unless each average in column A is based on the same n observations/measurements.  True?