# Thread: Complex Average Function (Excel 2003)

1. ## 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. ## 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. ## 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. ## 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
•