MrExcel Publishing
Your One Stop for Excel Tips & Solutions

STDEV if?


Posted by Kirby on December 01, 2001 6:47 PM

Can you do a standard deviation calculation on a set of numbers that is derived based on criteria? For example, assume A1:A5 is styleA, styleB, styleA, styleB, styleA and B1:B5 is 2, 4, 4, 7, 4. Is there a formula I can put in cell in column C that performs a STDEV calculation for just the numbers in column B that have "styleA" in the adjacent cell in column A?


Posted by Kirby on December 01, 2001 8:38 PM

Oops... Forgot Something...

Similar data to what's in columns A and B would also be repeated in other columns (i.e. styleA and styleB might also be in column D, F, and H with numbers beside them in columns E, G, and I). I'm seeing that will probably complicate things!

Posted by Aladin Akyurek on December 02, 2001 4:08 AM

Re: Oops... Forgot Something...

Kirby --

Lets say that houses the following sample data:

{"a",3,0,"a",8,"a",3,"a",3;"a",4,0,"a",9,"a",4,"a",4;"b",5,0,"b",4,"b",6,"b",3;"a",6,0,"b",5,"b",4,"a",6;"b",7,0,"a",6,"a",5,"a",7}

Zeroes are blank cells (your column C, which is empty).
"a" stands for styleA, "b" for "styleB".

Array-enter

=SQRT(((COUNTIF(A1:H5,"a")*SUM(IF((A1:H5="a")*(ISNUMBER(B1:I5)),(B1:I5^2))))-SUM(IF((A1:H5="a")*(ISNUMBER(B1:I5)),(B1:I5)))^2)/(COUNTIF(A1:H5,"a")*(COUNTIF(A1:H5,"a")-1)))

In order to array-enter a formula you need to hit CONTROL+SHIFT+ENTER at the same time, not just ENTER.

Note 1. You can use a cell of its own for the COUNTIF-part of this formula

=COUNTIF(A1:H5,"a")

and substitute the cell ref of this formula for the COUNTIF-part in the array-formula.

Note 2. Just a brief moment I thought of mailing you an invoice. :)

Enjoy.

Aladin

Posted by Kirby on December 02, 2001 8:26 AM

Re: Oops... Forgot Something...

WOW! That is QUITE a formula - I don't think I've ever seen one to match it! Thanks!