Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex Average Function (Excel 2003)

    Hello to all and thanks for so much help in the past. I'm back with another challenge for the board's Excel gurus.

    I need a formula that will evaluate a range of values and return the Average of the top 25% of the values (would this be the first quartile?), and another formula that will return the median from the top 25% of those values. And, of course, I am looking for equivalent formulas that will return both the average and the median from the 2nd 25% of numbers, the third 25% of the numbers and the bottom 25%. I feel like this should be relatively simple, but I have been unable to figure it out thus far.

    Thanks in advance for any and all suggestions...

    Thanks,

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Complex Average Function (Excel 2003)

    Assuming A1:A100 is datarange:
    The median is
    =PERCENTILE(A$1:A$100,50%)

    All of these are ARRAY (confirm with control-shift-enter)
    The top 25% is 4th quartile not first. The average is

    =AVERAGE(IF(($A$1:$A$100>=PERCENTILE(A$1:A$100,75% ))*ISNUMBER($A$1:$A$100),$A$1:$A$100))

    Median is:
    =PERCENTILE(IF(($A$1:$A$100>=PERCENTILE(A$1:A$100, 75%))*ISNUMBER($A$1:$A$100),$A$1:$A$100),50%)

    The rest can be calculated similarly: 2nd Quartile average is
    =AVERAGE(IF(($A$1:$A$100>=PERCENTILE(A$1:A$100,25% ))*($A$1:$A$100<PERCENTILE(A$1:A$100,50%))*ISNUMBE R($A$1:$A$100),$A$1:$A$100))

    I leave the rest to you.
    Steve

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Average Function (Excel 2003)

    Thanks, Steve. I was close, but I didn't think to multiply the hits for the percentile in the array formula by ISNUMBER. I tried a lot of variations and I knew I was close, but I just couldn't get the right answer.

    Thanks again. You bring a lot of relief to a lot of Excel lives...

    Regards,

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Complex Average Function (Excel 2003)

    The isnumber is essential if the list may have blanks or other non-numerics...

    Steve

Posting Permissions

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