Results 1 to 7 of 7

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

  1. #1

    Avg, Median, Max, and Min with Range

    Register for a FREE account, and/
    or Log in to avoid these ads!


    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 - <=$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. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider

    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.)

    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #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. #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.
    Attached Files Attached Files

  5. #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. #6
    Yes, those rows should be outsorted.

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

  7. #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))

Posting Permissions

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