Avg, Median, Max, and Min with Range

[PraveshG81]

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!

[Ken Puls]

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,

[Bob Phillips]

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

[PraveshG81]

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.

[Bob Phillips]

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?

[PraveshG81]

Yes, those rows should be outsorted.

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

[Bob Phillips]

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.