# Thread: MEDIANIF Function??? (Excel 2003 SP1)

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

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

Gentlemen,

Regards,
Teunis

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