Standard Deviation of a subset

[charlie1105]

I'm trying to create a column that shows the standard deviation of a subset. I'm going to use this to search for outlying values for each particular customer.

I.e. so I can set a flag if a particular invoice is a given amount away from the "normal" for that customer.

I'm nearly there, but my formula is including the zeros in the array when calculating the standard deviation, which produces the wrong answer.

Anyone know a work around?

Book1
	A	B	C	D
1	Customernumber	Amt	Stdevofsubset	WhatIwantittoshow
2	1	10	9.102589898	10
3	1	20	9.102589898	10
4	1	30	9.102589898	10
5	2	50	17.6944974	10.40833
6	2	45	17.6944974	10.40833
7	2	30	17.6944974	10.40833
8	3	10	6.363586889	5.033222957
9	3	20	6.363586889	5.033222957
10	3	14	6.363586889	5.033222957
11	4	100	89.89411761	90.42492282
12	4	300	89.89411761	90.42492282
13	4	100	89.89411761	90.42492282
14	4	80	89.89411761	90.42492282
15	4	200	89.89411761	90.42492282
16	4	70	89.89411761	90.42492282
Sheet1

 

[barry houdini]

Use IF instead, i.e.

=STDEV(IF($A$2:$A$16=A2,$B$2:$B$16))

confirmed with CTRL+SHIFT+ENTER

 

[charlie1105]

I knew it would be something simple!

Thanks for your help