Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a range in Excel like:

    A 1
    A 2
    B 3
    A 2
    C 1
    A 2
    B 3
    etc.

    In other words, different 'categories' with values.

    I want to display averages and StDevs for each category. Averages are simple: =Sumif(CatRange, "A", ValueRange) / CountIf(CatRange, "A")

    I'm having a little more problems doing an StDev for the categories in place. I know I should use an array formula and my starting point is:

    Sqrt(Sum(x-xavg)^2/(n-1)

    The (n-1) is the COUNTIF I used above minus one. I have the average, so the SUM of the (x - xavg) is what I need now. Of course the x-es used should be the x-as for the category...

    The array formula for that piece should look something like: {= SUM ((catrange="A") * ValueRange) }... I know I'm close (I think)... anyone who can gve me the last missing piece of information (or maybe a more elegant method of course)?

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can use the array formula of:
    =STDEV(IF($A$1:$A$7=D2,$B$1:$B$7)) where D2 = category

    You can use the same method to find the average:
    =AVERAGE(IF($A$1:$A$7=D1,$B$1:$B$7))

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Mexico City, D.F., Mexico
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There is a function that I like very much as it has never let me down, SUMPRODUCT. In your case, I propose:
    =SQRT(SUMPRODUCT(($B$2:$B$26-SUMIF($A$2:$A$26,C3,$B$2:$B$26)/COUNTIF($A$2:$A$26,C3))^2,($A$2:$A$26=C3)*1)/(COUNTIF($A$2:$A$26,C3)-1))
    $A$2:$A$26: the range with the categories a, b, c, etc.
    $B$2:$B$26: the range with the values
    C3: a cell containing a category, could also be an absolute value like "a"
    This eco-post is made of recycled electrons

Posting Permissions

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