+ Reply to Thread
Results 1 to 3 of 3

Median with criteria, price range and date range

  1. #1
    Registered User
    Join Date
    12-06-2013
    Location
    NY, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question 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.
    Attached Files Attached Files
    Last edited by Bravmik; 01-31-2014 at 01:14 AM. Reason: SOLVED!

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,135

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

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    12-06-2013
    Location
    NY, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Finding multiple Min/max/Median of range given certain criteria
    By brandnew22 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-19-2013, 09:33 AM
  2. [SOLVED] defining a range subset based on a primary range for use in Median and Mode functions
    By Araise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-06-2013, 06:39 PM
  3. Calc median over a date range.
    By welchs101 in forum Excel General
    Replies: 5
    Last Post: 10-05-2011, 09:32 AM
  4. Excel 2007 : Lookup of material price based on date range
    By posttoamit in forum Excel General
    Replies: 1
    Last Post: 02-18-2011, 08:58 AM
  5. median with date range
    By cstalker in forum Excel General
    Replies: 13
    Last Post: 04-06-2009, 05:40 AM

Bookmarks

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