Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •