Need help with median formula based on a set criteria

chon

New member
Joined
Oct 9, 2013
Messages
3
Reaction score
0
Points
0
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
 

Attachments

  • Dummy Workbook (Median Formula).xls
    24 KB · Views: 19

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
For others cross posted at: http://www.excelforum.com/excel-for...h-median-formula-based-on-a-set-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:

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
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.
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
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)))
 
Top