# Thread: Avg, Median, Max, and Min with Range

1. ## Avg, Median, Max, and Min with Range

Hello,

I want to find out the Average, Median Max and Min of the "Note Size" (Column B) if the Enterprise Value (Column A) is within the following range:

All Sizes
<\$25M
>\$25M - <=\$100M
>\$100M - <=\$250M
>\$250M - <=\$750M
>\$750M?Excel Question.XLSX

i tried doing the MEDIAN(IF(A2:A1000<25000000,B2:B1000) as an array but I numbers don't look right.

I have attached the file for your to look at (two tabs), please let me know what I am doing wrong.

Thank You!

2. Pravesh,

My apologies, but I'm away at the moment, with very limited time to pitch in to threads. If you can hold until Monday, then I can look at this in more detail. (Unless someone else picks it up earlier, of course.)

Cheers,

3. C3: =AVERAGE(IF(Results!\$A\$2:\$A\$10000<25000000,Results!\$B\$2:\$B\$10000))
D3: =AVERAGE(IF((Results!\$A\$2:\$A\$10000>=25000000)*(Results!\$A\$2:\$A\$10000<100000000),Results!\$B\$2:\$B\$10000))
E3: =AVERAGE(IF((Results!\$A\$2:\$A\$10000>=100000000)*(Results!\$A\$2:\$A\$10000<250000000),Results!\$B\$2:\$B\$10000))
F3: =AVERAGE(IF((Results!\$A\$2:\$A\$10000>=250000000)*(Results!\$A\$2:\$A\$10000<750000000),Results!\$B\$2:\$B\$10000))
G3: =AVERAGE(IF(Results!\$A\$2:\$A\$10000>=750000000,Results!\$B\$2:\$B\$10000))

C4: =MEDIAN(IF(Results!\$A\$2:\$A\$10000<25000000,Results!\$B\$2:\$B\$10000))
D4: =MEDIAN(IF((Results!\$A\$2:\$A\$10000>=25000000)*(Results!\$A\$2:\$A\$10000<100000000),Results!\$B\$2:\$B\$10000))

all array formulae, and I leave you to work out the rest - all the same principle

4. Thank you so much for your help, it all works except for the Median, i get all zeros, do you know why? Is it because I don't have data that goes all the way to 10,000? I have attached the updated file for your review.

5. You get zeroes because a lot of the matching values have no corresponding percentage. This means a lot of zeroes get included in the median calculation. Should those rows be outsorted just like the non-matching value rows?

6. Yes, those rows should be outsorted.

Thus the median for <\$25m (C4) would be 15.8% , D4 would be 26.1%.

7. Okay, then use

C4: =MEDIAN(IF((Results!\$A\$2:\$A\$10000<25000000)*(Results!\$B\$2:\$B\$10000<>""),Results!\$B\$2:\$B\$10000))
D4: =MEDIAN(IF((Results!\$A\$2:\$A\$10000>=25000000)*(Results!\$A\$2:\$A\$10000<100000000)*(Results!\$B\$2:\$B\$10000<>""),Results!\$B\$2:\$B\$10000))
etc.

#### Posting Permissions

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