Calculating the standard deviation of two weighted arrays.

[just_matt]

I have a formula that calculates the weighted average of two sets of data (F6:H6 and I6:K6) with weights defined in F3:H3 and I3:K3 respectively:

=SUMPRODUCT(F6:H6,$F$3:$H$3)*SUMPRODUCT(I6:K6,$I$3:$K$3)/(SUM($F$3:$H$3)*SUM($I$3:$K$3))

I am looking for a way to simplify my standard deviation calculation. Right now I can enter all data point calculations manually into a STDEV.P() or I can manually calculate the weighted standard deviation using:

=SQRT((SUM((F6*I6-$S$37)^2, (F6*J6-$S$37)^2*$G$3, (F6*K6-$S$37)^2, (G6*I6-$S$37)^2*$G$3, (G6*J6-$S$37)^2*$G$3*$J$3, (G6*K6-$S$37)^2*$G$3, (H6*I6-$S$37)^2, (H6*J6-$S$37)^2*$J$3, (H6*K6-$S$37)^2))/(SUM($F$3:$H$3)*SUM($I$3:$K$3)))

This returns 3.72015

My ask: Is there a way to simplify my standard deviation formula? OR ... am I doing this completely wrong?

ROW/COL	F​	G​	H​	I​	J​	K​
3​	1	4	1	1	4	1
6​	1	4	10	.5	.75	.9

My weighted average for the dataset returns 7.8222. If I'm wrong there, then I have bigger problems.

My manually calculated STDEV.P returns 3.72015

=STDEV.P(F6*I6, F6*J6, F6*J6,F6*J6,F6*J6,F6*K6, G6*I6, G6*I6,G6*I6,G6*I6, G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6, G6*K6,G6*K6,G6*K6,G6*K6, H6*I6, H6*J6,H6*J6,H6*J6,H6*J6, H6*K6)

Sorry for not being able to upload a minisheet.

 

[StephenCrump]

For the data you've posted, I get these results (assuming S37: =C3)

	A	B	C	D	E	F	G	H	I	J	K	L
1
2						Weights1			Weights2
3		Av	3.30			1	4	1	1	4	1
4		StDev1	2.07
5		StDev2	2.07			Data1			Data2
6						1	4	10	0.5	0.75	0.9
7
Sheet1

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(F6:H6,$F$3:$H$3)*SUMPRODUCT(I6:K6,$I$3:$K$3)/(SUM($F$3:$H$3)*SUM($I$3:$K$3))
C4	C4	=SQRT((SUM((F6*I6-$S$37)^2, (F6*J6-$S$37)^2*$G$3, (F6*K6-$S$37)^2, (G6*I6-$S$37)^2*$G$3, (G6*J6-$S$37)^2*$G$3*$J$3, (G6*K6-$S$37)^2*$G$3, (H6*I6-$S$37)^2, (H6*J6-$S$37)^2*$J$3, (H6*K6-$S$37)^2))/(SUM($F$3:$H$3)*SUM($I$3:$K$3)))
C5	C5	=STDEV.P(F6*I6, F6*J6, F6*J6,F6*J6,F6*J6,F6*K6, G6*I6, G6*I6,G6*I6,G6*I6, G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6,G6*J6, G6*K6,G6*K6,G6*K6,G6*K6, H6*I6, H6*J6,H6*J6,H6*J6,H6*J6, H6*K6)

But before we tackle weighted standard deviation, what exactly do you mean by weighted average? Your formula makes no sense to me. For example, this configuration gives weighted average 20:

Weights1			Weights2
1	1	1	1	1	1
Data1			Data2
2	2	2	10	10	10

And this one gives weighted average 30:

Weights1			Weights2
1	1	1	1	1	1
Data1			Data2
3	3	3	10	10	10

Is there some logic here that I'm missing?

 

[just_matt]

UGH.

Transposition error on my side. The math is correct, the inputs I typed in were wrong, shifted left by one column when entering the data by hand on the board here. My sincere apologies.

ROW/COL	F​	G​	H​	I​	J​	K​
3​	1	4	1	1	4	1
6​	4	10	20	.5	.75	.9

 

[StephenCrump]

Transposition error on my side.

No problem. I assumed that was what had happened.

My question was more about what your weighted average represented. But on closer look, I can see you're wanting to create a 3x3 data matrix and weightings, so please ignore my comments in post #2 about the logic.

I think this is what you're after?

	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2						Weights1			Weights2
3		Av	7.8222			1	4	1	1	4	1
4		StDevP	3.7201
5						Data1			Data2
6						4	10	20	0.5	0.75	0.9
7		Demonstrating in steps ....
8
9		Combined data				Variance				Combined weights
10		2.0	5.0	10.0		33.90	7.96	4.74		1	4	1
11		3.0	7.5	15.0		23.25	0.10	51.52		4	16	4
12		3.6	9.0	18.0		17.83	1.39	103.59		1	4	1
13
14		StDevP	3.7201
15			TRUE
16
Sheet1

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(F6:H6,$F$3:$H$3)*SUMPRODUCT(I6:K6,$I$3:$K$3)/(SUM($F$3:$H$3)*SUM($I$3:$K$3))
C4	C4	=SQRT(SUM((F6:H6*TRANSPOSE(I6:K6)-C3)^2*F3:H3*TRANSPOSE(I3:K3)/(SUM(F3:H3)*SUM(I3:K3))))
B10:D12	B10	=F6:H6*TRANSPOSE(I6:K6)
F10:H12	F10	=(B10-$C$3)^2
J10:L12	J10	=F3:H3*TRANSPOSE(I3:K3)
C14	C14	=SQRT(SUM(F10:H12*J10:L12)/(SUM(F3:H3)*SUM(I3:K3)))
C15	C15	=C14=C4
Press CTRL+SHIFT+ENTER to enter array formulas.

If you have the latest version of Excel, you won't need to array-enter any of these formulae. It's a good idea to update your Account details to let everyone know which version you're using, as the best solution may vary by Excel version.

 

[just_matt]

=SQRT(SUM((F6:H6*TRANSPOSE(I6:K6)-C3)^2*F3:H3*TRANSPOSE(I3:K3)/(SUM(F3:H3)*SUM(I3:K3))))

I sat too %&*#! close to the work to not see this. Now it's obvious. However I can say it probably would not have been obvious without your help. Greatly appreciated.

 

[StephenCrump]

Great! I'm glad we could help.