# Median with criteria, price range and date range

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

2. ## Re: Median with criteria, price range and date range

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

3. ## Re: Median with criteria, price range and date range

Thank you VERY much. This works for every price range, with or without the date.

The removal of the extra set of \$E\$14:\$E\$34 makes perfect sense in hindsight, as I only needed to name the return range once. Because I've never done a price range with a MEDIAN, I didn't know the double quotes should be removed... definitely won't forget! Thank you again!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1