# Thread: Conditional Std Dev Calculation (Excel 2000, SR1)

1. ## Conditional Std Dev Calculation (Excel 2000, SR1)

(I know Bob Umlas must know this one...)

I have a list of numbers, each associated with a label (like "A" or "B"), like so:
<pre>A 1.234
B 4.321
A 2.345
B 2.431
etc.
</pre>

I need to calculate various statistics for "A" data alone and "B" data alone. I know I can calculate average as follows:

<pre>{=SUM(IF(A1:A10="A",B1:B10,0))/SUM(IF(A1:A10="A",1,0))}
</pre>

Is there a way to use the AVERAGE worksheet function in such an array fashion, rather than dividing two SUM results? I also need to calculate STDEV...

Any takers? Thanks!

2. ## Re: Conditional Std Dev Calculation (Excel 2000, SR1)

{=AVERAGE(IF(A1:A10="A",B1:B10,""))}
{=STDEV(IF(A1:A10="A",B1:B10,""))}
{=MAX(IF(A1:A10="A",B1:B10,""))}

Etcetera.

3. ## Re: Conditional Std Dev Calculation (Excel 2000, SR1)

Jan,

Thanks!

At first glance the STDEV looks like it should work with this approach. However (suppose there are ten cells, 5 of each A and [img]/forums/images/smilies/cool.gif[/img], instead of calculating STDEV for the five A's with n=5, it calculates STDEV with the five A's, five zeros, and n=10, which gives an incorrect result. See attached sample.

I figured out a way to do this with the DSTDEV, also in attached. I will implement that method for now.

4. ## Re: Conditional Std Dev Calculation (Excel 2000, SR1)

If you do as Dr. Jan prescribed:
<pre>{=STDEV(IF(A5:A14="B",B5:B14,""))}</pre>

it will work just fine.

5. ## Re: Conditional Std Dev Calculation (Excel 2000, SR1)

Argh - see what happens when you have too much beer the night before?!? Thanks! You both are quite correct!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•