Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have data in two columns, date ascending in column A, data in column B.

    I would like to determine the median between two date ranges; e.g., 10/1/1999 to 9/30/2002. Dates are not necessarily consecutive; e.g. I may or may not have data for either of those dates.

    My feeble attempt is attached. It would be greatly appreciated if you could assist in getting the correct formula
    thanks for the help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your formulas are overkill: DATE(YEAR(x),MONTH(x),DAY(x)) is equivalent to x.
    To express an AND condition in this type of formula, you can multiply the conditions; AND(...) will not work.
    The formula for F3 becomes:

    =MEDIAN(IF(($A$2:$A$6541>=D3)*($A$2:$A$6541<=E3),$ B$2:$B$6541))

    Confirm with Ctrl+Shift+Enter, then fill down.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799562' date='23-Oct-2009 16:45']Your formulas are overkill: DATE(YEAR(x),MONTH(x),DAY(x)) is equivalent to x.
    To express an AND condition in this type of formula, you can multiply the conditions; AND(...) will not work.
    The formula for F3 becomes:

    =MEDIAN(IF(($A$2:$A$6541>=D3)*($A$2:$A$6541<=E3),$ B$2:$B$6541))

    Confirm with Ctrl+Shift+Enter, then fill down.[/quote]

    Still trying to figure out array formulas....
    THANKS!

Posting Permissions

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