If you want a Median of an array, (i'll say A1:A10) not counting zero
values, this is how you do it. (I'm refering to my example)
1 5
2 6
3 7
4 0
5 0
6 4
7 3
8
9 4
10 6
Cells like A8 with blank values must be turned into 0, hence B1 takes
this equation:
=IF(A1="",0,A1)
C1 should take this equation:
=IF(B1=0,0,1)
Drag these formulae down to the bottom of the table
C11 can be =SUM(C1:C10)
Cell A11 can also be =SUM(A1:A10)
Cell C12 can take the equation
=(C12+IF(MOD(C11,2)=1,1,0))/2
Cell C12 tells you the position of the median within the range
Highlight the entire table (except the sums below) and sort by column B
in descending order.
Column D can take numbered values, 1, 2, 3... with respect to the row
number.
Now cell E1 takes this equation:
=IF(C$12=D1,B1,0)
Drag this column down to the bottom of the table
The cell that will give the median takes the equation:
=SUM(E1:E10)
Long winded, but it will give you the solution.
--
Faz
------------------------------------------------------------------------
Faz's Profile:
http://www.excelforum.com/member.php...o&userid=27830
View this thread:
http://www.excelforum.com/showthread...hreadid=475831