Combine standard deviations

[abousetta]

Hello,

I'm trying to replicate the formulas from here in order to calculate the weighted standard deviation from different samples. The formula is as follows:

image026.gif

I've put together an example workbook and isolated the problem that the error is caused by trying to get the square root of a negative number. The culprit seems to be:

- (3*A2*D2*G2)

Can anyone help me to correct this formula?

Thanks.

abousetta

[shg]

Assuming N1 and N2 are >1, the number inside the radical can't be negative in that formulation. Can you give an example?

EDIT: NVM, just saw the attachment -- hang on ...

[plotting]

Put ABS after SQRT like so =SQRT(ABS(((C2-1)*B2^2 + (F2-1)*E2^2 + (I2-1)*H2^2+(C2*F2*I2)/(C2+F2+I2) * (A2^2 + D2^2 + G2^2 - (3*A2*D2*G2)))/(C2+F2+I2-1))) for cell K2

[abousetta]

Thanks. Yes, there's something wrong, but I can't put my finger on it. I might be that I'm over-extending the formula since the example they show it for two samples/ SDs and maybe you have to calculate for each pair then get the weighted mean/ SD then repeat for the third sample. I'm not sure???

[abousetta]

@plotting, thank you for your suggestion, but the resulting SD is too high for it to be correct (e.g. 4535.7 in K2).

[plotting]

If you evaluate the formula of K2 without adding ABS you get -20572691.5977404. SQRT of 20572691.5977404 is 4535.71290954. I can look at the formula and see if it's calculating incorrectly.

[abousetta]

Thanks shg. Always appreciate your insight.

[abousetta]

OK. Thanks. Back to the drawing board

[shg]

The formula you posted is for combining two distributions, not three. How did you plan to do that?

[abousetta]

I was trying to expand that formula, but I guess then my attempt failed.

Therefore, I'm assuming that I have to create a repeated iteration... combine sample 1 and 2 then combine the resultant with sample 3.

Is this correct?

[shg]

That's not obvious to me, but in fact, http://handbook.cochrane.org/chapter...ing_groups.htm says just that:

If there are more than two groups to combine, the simplest strategy is to apply the above formula sequentially (i.e. combine group 1 and group 2 to create group ‘1+2’, then combine group ‘1+2’ and group 3 to create group ‘1+2+3’, and so on).

[shg]

Here's what I get:

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	n(1)	u(1)	s(1)		n(2)	u(2)	s(2)		n(1+2)	u(1+2)	s(1+2)		n(3)	u(3)	s(3)		n(1+2+3)	u(1+2+3)	s(1+2+3)
2	438	62.00	13.00		427	65.60	14.40		865	63.78	13.82		140	66.00	14.20		1,005	64.09	13.89
3	193	67.00	14.60		76	66.70	14.60		269	66.92	14.57		127	65.00	13.00		396	66.30	14.10
4	29	55.80	11.90		586	60.90	13.60		615	60.66	13.56		1,434	64.00	12.00		2,049	63.00	12.58
5	391	71.00	14.00		1,095	67.00	2.00		1,486	68.05	7.58		592	62.00	13.20		2,078	66.33	9.91
6	209	61.80	13.30		378	61.75	11.45		587	61.77	12.13		73	63.70	14.40		660	61.98	12.40
7	1,054	63.00	13.00		2,288	61.40	12.12		3,342	61.90	12.42		241	61.50	12.60		3,583	61.88	12.44
8	127	55.60	11.30		145	66.40	10.50		272	61.36	12.13		420	63.30	13.60		692	62.54	13.07
9	241	59.60	13.70		218	60.80	13.20		459	60.17	13.46		139	58.00	11.03		598	59.67	12.96
10	50	54.50	11.60		258	62.00	11.00		308	60.78	11.42		57	63.10	13.40		365	61.14	11.76
11	202	63.00	13.00		44	59.00	16.00		246	62.28	13.64		821	62.90	12.80		1,067	62.76	12.99
12	28	62.00	12.00		263	64.00	12.40		291	63.81	12.36		76	66.60	11.40		367	64.39	12.20

I2 and down: =A2+E2

J2 and down: =(A2*B2 + E2*F2) / I2

K2 and down: =SQRT(((A2-1)*C2^2 + (E2-1)*G2^2 + A2*E2/I2 * (B2-F2)^2) / (I2-1))

Then copy to Q2:S2

[abousetta]

Thanks so much. It's working as expected.

Cheers,

abousetta

[shg]

You're more than welcome.