Median with criteria, price range and date range

Hello,

I am trying to find the median for a huge amount of data, so I have cut it down to just an example size. I have uploaded a copy of the example file.

I have three price ranges that I have to find the median for:

Under $5MM
$5 MM - $25 MM
$26 MM - $56 MM

I need to find the median for each price range for Signed units and Sold units. I also have to find a median within a date range (e.g. within the last six months) that includes the previous criteria.


For the first formula, I have tried using the following formulas:

=MEDIAN(IF($I$14:$I$34,"Signed",IF($F$14:$F$34,">="&0,IF($F$14:$F$34,"<="&5000000,$F$14:$F$34))))

=MEDIAN(IF($I$14:$I$34,"Signed",IF($F$14:$F$34,">=0",IF($F$14:$F$34,"<=5000000",$F$14:$F$34))))

(I remembered to do "CTL + SHIFT and ENTER" to make it an array.)


For the second formula, with the date range, I am able to find the total for the last six months, but not with a price range included.

The formula that I am using that works for the median of units Signed within the last six months is:

=MEDIAN(IF($H$14:$H$34="Signed",IF($G$14:$G$34>"07/31/2013"+0,$E$14:$E$34)))

However, once I try to find the price range, it does not work:

=MEDIAN(IF($H$14:$H$34="Signed",IF($G$14:$G$34>"07/31/2013"+0,$E$14:$EIF$34,IF($E$14:$E$34,">=0",IF($E$14:$E$34,"<=5000000",$E$14:$E$34)))))


Any insight given would be much appreciated.

hi there. a few pointers:
1. dont't use 07/31/2013. it wont work for all regions. mine's DMY. it's also confusing when the day is less than 13. use "31jul2013".
2. double quotes with operators like ">=0" & "<=5000000" only works in the COUNTIF, SUMIF, AVERAGEIF formulas.
3. you should continue the IFs instead of returning $E$14:$E$34 3 times.

so:
=MEDIAN(IF($H$14:$H$34="Signed",IF($G$14:$G$34>"31jul2013"+0,IF($E$14:$E$34>=0,IF($E$14:$E$34<=5000000,$E$14:$E$34)))))