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!
