Median of Even Set; How to Capture them?

[Mike]

Hi everyone,

Say I have a dat set such as:

4
5
6
7

The median is (5+6)/2=5.5!

However, I am not interested in the answer here, but the 5 and the 6!!!

Is there an excel function or way to do so?

Thanks alot,
Mike

[Morrigan]

Do you want the output in one single cell or in 2 separate cells?

Hi everyone,

Say I have a dat set such as:

4
5
6
7

The median is (5+6)/2=5.5!

However, I am not interested in the answer here, but the 5 and the 6!!!

Is there an excel function or way to do so?

Thanks alot,
Mike

[olasa]

Here is one solution to find "the Reversed Median".

Value One:
=IF(ISODD(COUNT(A1:A6)),INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6)+1)/2),INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6))/2))

Value Two:
=IF(ISODD(COUNT(A1:A6)),"",INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6)+2)/2))

Example:
1, 2, 2, 4, 5, 6 --> 2 and 4
1, 2, ,4, 5, 6 --> 4

Hope it helped
Ola Sandström


Attached zip-file: http://www.excelforum.com/attachment...tid=3648&stc=1

[Mike]

Morrigan,

I want them in TWO different cells........thanks,

Mike

[Mike]

Olasa,

Tried your formula, but none of them did work!

Have you tried them on an example to see if they are working as they
appear above?

Mike

[Jerry W. Lewis]

Those are awfully complex formulas for a simple calculation. Why not
=LARGE(A1:A6,COUNT(A1:A6)/2)
for the median (if n odd) or the larger middle value (if n even), and
=IF(ISEVEN(COUNT($A$1:$A$6)),SMALL($A$1:$A$6,COUNT($A$1:$A$6)/2),"")
for the smaller middle value (if n even)

Jerry

olasa wrote:

> Here is one solution to find "the Reversed Median".
>
> Value One:
> =IF(ISODD(COUNT(A1:A6)),INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6)+1)/2),INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6))/2))
>
> Value Two:
> =IF(ISODD(COUNT(A1:A6)),"",INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6)+2)/2))
>
> Example:
> 1, 2, 2, 4, 5, 6 --> 2 and 4
> 1, 2, ,4, 5, 6 --> 4
>
> Hope it helped
> Ola Sandström

[Mike]

Jerry,

I agree, your 1st formula is working fine but the 2nd one is NOT!?

Could you please check it?

Thanks,
Mike

[Mike]

Jerry,

Yours are BOTH working now.....thank you ALL

Thanks alot,
Mike