MrExcel Publishing
Your One Stop for Excel Tips & Solutions


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:


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



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


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. :)



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!