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!
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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

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

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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

New member
Joined
Oct 6, 2011
Messages
22
Reaction score
0
Points
0
Yes, those rows should be outsorted.

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

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 
Top