Median of Even Set; How to Capture them?

M

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
 
O

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.php?attachmentid=3648&stc=

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

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
 
J

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
 
M

Mike

Jerry,

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

Could you please check it?

Thanks,
Mike
 
Top