Avg, Median, Max, and Min with Range

PraveshG81

New member
Joined
Oct 6, 2011
Messages
22
Reaction score
0
Points
0
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?View attachment 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!
 
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,
 
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
 
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.
 

Attachments

  • Copy of Excel Question.XLSX
    13.2 KB · Views: 8
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?
 
Yes, those rows should be outsorted.

Thus the median for <$25m (C4) would be 15.8% , D4 would be 26.1%.
 
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.
 
Back
Top