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

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


Mike Wrote:
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
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=390763

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

Value One:
=IF(ISODD(COUNT(A1:A6)),INDEX(LARGE(A1:A6,ROW(INDI RECT("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(I NDIRECT("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


+-------------------------------------------------------------------+
|Filename: Book5.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3648 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=390763

Morrigan,

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

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

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(INDI RECT("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(I NDIRECT("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

Jerry,

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

Could you please check it?

Thanks,
Mike

Jerry,

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

Thanks alot,
Mike