STDEV equivalent to AVERAGEIFS

[Desoxyn]

I have a data that I'm calculating the average for a group of numbers using code below. Column P is the average of column N corresponding to the number in column C. See attached image. How do I calculate the standard deviation equivalent?

AVERAGEIFS(N:N,C:C,C2,N:N,"<>"&"")

 

[AhoyNC]

Try:
Change ranges to match your data. It's usually best not to reference whole columns.

Book1
	A	B	C										M	N
1	St. Dev.		Data
2	124.791		4											500
3			4											250
4			4											300
5			5											375
6			6											400
7			4
8			4											475
Sheet2

Cell Formulas
Range	Formula
A2	A2	=STDEV.S(IF(C2:C8=C2,IF(N2:N8<>"",N2:N8)))

 

[Desoxyn]

Try:
Change ranges to match your data. It's usually best not to reference whole columns.

Book1
	A	B	C										M	N
1	St. Dev.		Data
2	124.791		4											500
3			4											250
4			4											300
5			5											375
6			6											400
7			4
8			4											475
Sheet2

Cell Formulas
Range	Formula
A2	A2	=STDEV.S(IF(C2:C8=C2,IF(N2:N8<>"",N2:N8)))

Hello,

Thanks for the suggestion. Unfortunately it didn't work, I narrowed the range to 10k but the stdev column should have one value for each cell corresponding to the average. :\

 

[AhoyNC]

Did you get an error msg. , wrong answer??
As you can see it worked in the above example. What is the formula you entered?

 

[Desoxyn]

Did you get an error msg. , wrong answer??
As you can see it worked in the above example. What is the formula you entered?

It's in my attached image.
=STDEV(IF(C2:C10000=C2,IF(N2:N10000<>"",N2:N10000)))

No error but the stdev column should have the same value for each cell corresponding to its data. For example, in that image, you can see that the Wafer ID (column C) is 117, the average of Loading (column N) is outputted in Average (column P) as 4.519293. It's repeated the same for the rest of the cells corresponding to the same Wafer ID. Column Q should have stdev the same corresponding to its average. The value changes with a new Wafer ID (in my case, 113).

 

[AhoyNC]

I think it will work if you use absolute references (locked cells).
=STDEV(IF($C$2:$C$10000=C2,IF($N$2:$N$10000<>"",$N$2:$N$10000)))

Book1
	A	B	C										M	N	O	P
1			Data													St. Dev.
2			4											500		124.791493
3			4											250		124.791493
4			4											300		124.791493
5			5											375		17.6776695
6			6											400		67.1751442
7			4													124.791493
8			4											475		124.791493
9			5											400		17.6776695
10			6											495		67.1751442
Sheet2

Cell Formulas
Range	Formula
P2:P10	P2	=STDEV.S(IF($C$2:$C$10=C2,IF($N$2:$N$10<>"",$N$2:$N$10)))

 

[Desoxyn]

I think it will work if you use absolute references (locked cells).
=STDEV(IF($C$2:$C$10000=C2,IF($N$2:$N$10000<>"",$N$2:$N$10000)))

Book1
	A	B	C										M	N	O	P
1			Data													St. Dev.
2			4											500		124.791493
3			4											250		124.791493
4			4											300		124.791493
5			5											375		17.6776695
6			6											400		67.1751442
7			4													124.791493
8			4											475		124.791493
9			5											400		17.6776695
10			6											495		67.1751442
Sheet2

Cell Formulas
Range	Formula
P2:P10	P2	=STDEV.S(IF($C$2:$C$10=C2,IF($N$2:$N$10<>"",$N$2:$N$10)))

That worked, thank you very much!!

 

[AhoyNC]

You're welcome. Thanks for the feedback.