STDEV if formula help, thanks!

[e_sauven]

Hi

I am trying to adjust my average if formula =AVERAGEIF($H$H,DO13,AB:AB)) into also showing me the STDEV for the same values but I keep getting an error.

$H$H is the list of Names in my database, DO13 is the name in the table I want it to match with and AB:AB are the values I would like to get the STDEV from.

Many thanks for any help!

Emilie

 

[Andrew Poulsom]

You can use a formula like:

=STDEV(IF(H1:H100=DO13,AB1:AB100))

confirmed with Ctrl+Shift+Enter. If entered correctly Excel will surround the formula with curly braces {}. Avoid using entire columns.

 

[steve the fish]

Using the same ranges you could try this:

=STDEV(IF(H1:H10=DO13,AB1:AB10))

Its an array formula so need entering CNTL-SHIFT-ENTER. Look for the curly brackets.

Adjust the ranges to suit your data. Full column references are very slow for array formulas so should be avoided.

 

[e_sauven]

You can use a formula like:

=STDEV(IF(H1:H100=DO13,AB1:AB100))

confirmed with Ctrl+Shift+Enter. If entered correctly Excel will surround the formula with curly braces {}. Avoid using entire columns.

Thank you very much for your help!!

 

[e_sauven]

Thank you very much!! All working

You can use a formula like:

=STDEV(IF(H1:H100=DO13,AB1:AB100))

confirmed with Ctrl+Shift+Enter. If entered correctly Excel will surround the formula with curly braces {}. Avoid using entire columns.

 

[seriD]

Using the same ranges you could try this:

=STDEV(IF(H1:H10=DO13,AB1:AB10))

Its an array formula so need entering CNTL-SHIFT-ENTER. Look for the curly brackets.

Adjust the ranges to suit your data. Full column references are very slow for array formulas so should be avoided.

Hello,

I have a similar problem, but this formula doesnt seem to work yet for me.

So I have a list with lots of values (column A) with a score (column B), and applied the =unique function to gets each unique value (column E), then to get the average of each of these values I used =AVERAGEIF(A:A;E1;B:B).
Now to get a standard deviation for each of these unique values, I thought to use then
=STDEV(IF(A1:A595=E1,AB1:AB595)), but that doesnt work...
Any idea why, and how to fix this maybe someone?

Thanks for all help in advance

 

[AhoyNC]

Maybe something like this.
I'm assuming you have Excel 365 since you have the UNIQUE function.
I would suggest you not use whole column ranges, but only range the holds your data or a little beyond the bottom row of your data.
Also, instead of posting "that doesn't work" it is helpful if you let us know if you are getting an error msg, wrong answer???

Book2
	A	B	C	D	E	F
1	Values	Score			Unique	St. Dev.
2	1	45			1	14.5717
3	2	55			2	7.6376
4	3	26			3	2.0000
5	1	38
6	2	60
7	3	30
8	1	66
9	2	45
10	3	28
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(A2:A10)
F2:F4	F2	=STDEVA(FILTER(B:B,A:A=E2,"No Data"))
Dynamic array formulas.

 

[seriD]

Maybe something like this.
I'm assuming you have Excel 365 since you have the UNIQUE function.
I would suggest you not use whole column ranges, but only range the holds your data or a little beyond the bottom row of your data.
Also, instead of posting "that doesn't work" it is helpful if you let us know if you are getting an error msg, wrong answer???

Book2
	A	B	C	D	E	F
1	Values	Score			Unique	St. Dev.
2	1	45			1	14.5717
3	2	55			2	7.6376
4	3	26			3	2.0000
5	1	38
6	2	60
7	3	30
8	1	66
9	2	45
10	3	28
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(A2:A10)
F2:F4	F2	=STDEVA(FILTER(B:B,A:A=E2,"No Data"))
Dynamic array formulas.

Ok, thanks for your reply.

Your example of how my data looks like and what I want to do is spot on.

So for the first formula, mentioned earlier in this thread, to calculate the standard deviation =STDEV(IF(H1:H10=DO13,AB1:AB10)) I altered this one to =STDEV(IF(A1:A49=E2,B1:B49)), where I first did =UNIQUE(A:A) in the E column. Excel then just gives a pop up and says there is a problem with the formula.

Your suggested formula (=STDEVA(FILTER(B:B,A:A=E2,"No Data")) works perfectly, however I had to take out the comma's and replace them for a semicolon: =STDEVA(FILTER(B:B;A:A=E2;"No Data"))

Thank you very much, this saves me a lot of time!