Need help with median formula based on a set criteria

[chon]

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

[p45cal]

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

[p45cal]

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.

[p45cal]

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