+ Reply to Thread
Results 1 to 4 of 4

Calculate Median with two set of criteria that are date?

  1. #1
    Registered User
    Join Date
    10-02-2015
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    2

    Calculate Median with two set of criteria that are date?

    Hello everyone,
    I really need your help I have tried every combination there is always something that glitches

    I have noticed there is no such thing as medianifs unlike averageifs

    l have column C which indicate sales made, column D which indicates some dates when those sales when made

    I would like to have the median of the sales between lets say 01/02/2015 and 01/04/2015
    also i am using variable for that, so we have H6 which contains 01/02/2015 and H7=01/04/2015 its important to use the variables in the formula so i can copy accross (in other word (>=H6 and <=H7 ) something like that

    That would be nice if that formula could be used in googlespread sheet as well

    Many many thanks I have tried everything

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,572

    Re: Calculate Median with two set of criteria that are date?

    You can combine MEDIAN and IF in an array formula.

    =MEDIAN(IF($A$1:$A$20>=H6,IF($A$1:$A$20<=H7,$B$1:$B$20)))

    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...

    see sheet...
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    10-02-2015
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Calculate Median with two set of criteria that are date?

    Many thanks I ve read thousand of stuff online, I got the right stuff only thanks to you. I have added reputation for you accordingly.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    35,572

    Re: Calculate Median with two set of criteria that are date?

    Thanks for that. Glad to have helped.

+ 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. [SOLVED] Using Median IF to calculate the median for a specific criteria
    By boynejs in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-20-2014, 01:50 AM
  2. [SOLVED] calculate a MEDIAN using multiple criteria?
    By MetricsShiva in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  3. calculate a MEDIAN using multiple criteria?
    By MetricsShiva in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  4. calculate a MEDIAN using multiple criteria?
    By MetricsShiva in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 11:05 AM
  5. calculate a MEDIAN using multiple criteria?
    By MetricsShiva in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  6. [SOLVED] calculate a MEDIAN using multiple criteria?
    By MetricsShiva in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. calculate a MEDIAN using multiple criteria?
    By MetricsShiva in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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