Combining Standard Deviations

Originally Posted by eyemdtechgeek

Another thread from 2015 on here could do this, but again just for two means and SDs rather than an N

Actually, shg's method works for any N sets of n, mean and (population) sd. The better link is https://www.excelforum.com/excel-for...eviations.html .

We just have to understand his presentation, namely: 12 groups of 3 sets of data each(!?).

Thus, read across each line to see the method that could be applied to your problem.

Also, we must use the correct method for the desired type of std dev ("population" or "sample"). See the important notes below.

IMHO, that is not clear in the math formulas at atozmath.com. The better link is http://atozmath.com/example/CONM/Ch2...edSD.aspx?he=e .


-----

Applied to your design, see columns E:H in the attached file.

Refer to the "stdevp" or "stdev" worksheet, whichever applies to you. The following refers to the "stdevp" worksheet.

Essentially, shg's method is applied pairwise, where one of the pair is the combined n, mean and sd for the previous pair.

Thus, the formulas in E5:H5 are dragged down their respective columns for as many studies as you have. (Four in my example.)

(E4:H4 are simply a copy of the statistics for the first study in B4:D4.)

	A	B	C	D	E	F	G	H
3	study id	n	mean	std dev	comb n	comb mean	comb sd 1	comb sd 2
4	1	11	50.2727	26.0353	11	50.2727	26.0353	26.0353
5	2	13	556.2308	220.2691	24	324.3333	300.2424	300.2424
6	3	17	5608.4118	2742.3523	41	2515.2927	3154.0324	3154.0324
7	4	19	49015.8947	26985.2518	60	17240.4833	26557.3834	26557.3834

HTML Code: 

As proof of concept, see the actual combined n, mean and sd values in J2:O11 for the example study data in K13:N32.

See the important notes in the textbox near X8. Referring to the math formulas at http://atozmath.com/example/CONM/Ch2...edSD.aspx?he=e :


A. For Method 1, σ^2 refers to the "population" std dev (STDEVP).

It can be coverted for the "sample" std dev (STDEV), usually denoted by s,
by replacing σ^2 with s^2*(N-1)/N and by multiplying num/denom by (N1+N2)/(N1+N2-1).
Algebraically, this is equivalent to:

1. Change the numerator to: (N1-1)*s1^2 + N1*d1^2 + (N2-1)*s2^2 + N2*d2^2

2. And change the denominator to: N1+N2-1

Refer to the formula for G5 in the "stdev" worksheet.


B. For Method 2, σ^2 refers to the "sample" std dev (STDEV), usually denoted by s .

It can be converted for the "population" std dev (STDEVP) by replacing σ^2 with σ^2*N/(N-1)
and by multiplying num/denom by (N1+N2-1)/(N1+N2). Algebraically, this is equiivalent to:

1. Replace N1-1 with N1 and N2-1 with N2 in the numerator

2. And replace N1+N2-1 with N1+N2 in the denominator

3. Also, x1^2 + x2^2 - 2*x1*x2 can be simplified to (x1-x2)^2, as shg did

Refer to the formula for H5 in the "stdevp" worksheet.

-----

PS.... If you do not like the idea of iterative calculations in columns E:H, we could provide a VBA function that can referenced in a single cell. I don't have time to implement that now. But would that appeal to you?