Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    MEDIANIF Function??? (Excel 2003 SP1)

    I need to come up with a formula that will work like the SUMIF function, except that I need to calculate the median rather than the sum. Specifically, column A (formatted for numbers) contains a long list of various numbers representing military time that are in no special order. Column B contains a list of numbers representing measurements that were taken at the corresponding time in column A. For example --

    Column A Column B
    0.1354 152
    0.7083 133
    0.5625 148
    0.9583 147
    0.3438 128

    I need to determine the median measurement that was taken within various time frames. For example, the median of measurements taken between 0.3438 and 0.7083 (column A).

    This data is part of a large complicated worksheet and new entries are added daily that must be incorporated into the calculations. The data cannot be moved or sorted. The SUMIF function would be perfect if only it calculated the median rather than the sum.

    Robert

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MEDIANIF Function??? (Excel 2003 SP1)

    Assume that you want to calculate the median of values in A1:A5 for which the corresponding value in B1:B5 is between 128 and 148. Try the following as an array formula, i.e. confirm with Ctrl+Shift+Enter, not just with Enter.
    <code>
    =MEDIAN(IF((B1:B5>=128)*(B1:B5<=148),A1:A5))
    </code>
    Adjust the range as needed.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MEDIANIF Function??? (Excel 2003 SP1)

    Hans,

    This seems to me a very basic solution for this type of problems. I.e. MEDIAN could be almost any standard function, e.g. AVERAGE, SUM, TAN, etc. Is this correct?

    Regards,
    Teunis

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MEDIANIF Function??? (Excel 2003 SP1)

    Well, not TAN, since TAN only takes one argument.

    You can use this with the summary functions COUNT, COUNTA, SUM, PRODUCT, MIN, MAX, AVERAGE, VAR, VARP, STDEV and STDEVP, since these ignore blank values in the argument list.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: MEDIANIF Function??? (Excel 2003 SP1)

    The one thing to be aware of is that these can be very calculation intensive and can make your workbook "sluggish" if you have a lot of arrays or the lists are very large.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MEDIANIF Function??? (Excel 2003 SP1)

    Gentlemen,

    Thanks for your remarks.

    Regards,
    Teunis

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Re: MEDIANIF Function??? (Excel 2003 SP1)

    Hans,

    Worked like a charm. Thank you very much for your help.

    Best regards,

    Robert

Posting Permissions

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