Results 1 to 4 of 4

Thread: Need help with median formula based on a set criteria

  1. #1

    Need help with median formula based on a set criteria



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

    Hi guys,

    Hope you can help me with this. I need to create a couple of median formulas for events based on the following criteria; however, I'm unsure of how to do this. Refer to attached dummy workbook.

    First median formula required:

    • I want to find out the median of the event loss amounts (column G) for Australia (column D) with 'open' status (column N).
    • I want a formula to include the 'blank' cells in the median calculation and another formula to exclude the 'blank' cells in the median calculation.
    • Please note that some events will have identical loss amounts and some events will have loss amounts as 'blank' in the cell (NOT represented by zero).


    Second median formula required:

    • I want to find out the median of the event loss amounts (column G) for Australia (column D) only.
    • I want a formula to include the 'blank' cells in the median calculation and another formula to exclude the 'blank' cells in the median calculation.
    • Please note that some events will have identical loss amounts and some events will have loss amounts as 'blank' in the cell (NOT represented by zero).


    Thanks in advance for you help. Really appreciate it.

    Kind regards,

    Charles
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,398
    Articles
    0
    Excel Version
    365
    For others cross posted at: http://www.excelforum.com/excel-form...-criteria.html

    For chon, some light reading: http://www.excelguru.ca/content.php?184

    It would be nice of you to include links to all cross posts at all sites. (I for one, refrain from helping a cross poster a second time if they don't.)

    These two formula are to be array-entered (ctrl+shift+enter, not just enter):
    =MEDIAN(IF($D$3:$D$20="Australia",IF(LEN($G$3:$G$20)=0,FALSE,$G$3:$G$20)))
    =MEDIAN(IF($D$3:$D$20="Australia",IF(LEN($G$3:$G$20)=0,0,$G$3:$G$20)))

    the blue 0 formula counts blanks as zeroes, the blue FALSE formula disregards blanks in the median calculation. (In your sample data, the results are the same.)
    Last edited by p45cal; 2013-10-15 at 10:06 PM.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,398
    Articles
    0
    Excel Version
    365
    I missed the bit about Open in column N. Check these give the right results:
    =MEDIAN(IF(($D$3:$D$20="Australia")+($N$3:$N$20="Open"),IF(LEN($G$3:$G$20)=0,FALSE,$G$3:$G$20)))
    =MEDIAN(IF(($D$3:$D$20="Australia")+($N$3:$N$20="Open"),IF(LEN($G$3:$G$20)=0,0,$G$3:$G$20)))

    come back if they don't.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,398
    Articles
    0
    Excel Version
    365
    oops, those + symbols should be *s:
    =MEDIAN(IF(($D$3:$D$20="Australia")*($N$3:$N$20="Open"),IF(LEN($G$3:$G$20)=0,FALSE,$G$3:$G$20)))
    =MEDIAN(IF(($D$3:$D$20="Australia")*($N$3:$N$20="Open"),IF(LEN($G$3:$G$20)=0,0,$G$3:$G$20)))

Posting Permissions

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