Aggregate Mean and Std Dev

[bsrivatsa]

Hi,

Does Excel have the formula to calculate aggregate mean and standard deviation for a data set whose subpopulation Ns, means and SDs are known?
Please see the attached example. I need values for Mean and SD for the total sample in row 28.

Thanks for your help in advance.

Bharath

[hrlngrv]

The aggregate mean, E[X], is simple: dot product of sample mean and sample size arrays divided by sum of sample size array.

Please Login or Register  to view this content.

Aggregate standard deviation is more involved. Assuming each sample's variance were based on dividing by the sample's N-1 rather than N, I believe it should be

SD[X_s]² = Var[X_s] = sum((X_si - E[X_s])²) / (N_s - 1)

(N_s - 1) SD[X_s]² = sum((X_si - E[X_s])²) = sum(X_si²) - N_s E[X_s]²

sum(X_si²) = (N_s - 1) SD[X_s]² + N_s E[X_s]²

Var[X] = sum_s(sum(X_si²)) / sum_s(N_s) - E[X]²

Please Login or Register  to view this content.

[bsrivatsa]

Perfect! I am so grateful to you hrlngrv!! Thank you.