Calculat the MEDIAN of the ratio of two set data....

[jackyuen]

[TABLE="width: 50"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD]ratio
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]14
[/TD]
[TD][/TD]
[TD]1.21
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD]0.55
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]16
[/TD]
[TD][/TD]
[TD]0.31
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]18
[/TD]
[TD][/TD]
[TD]0.28
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD]#div/0!
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]17
[/TD]
[TD][/TD]
[TD]0.71
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD]1.5
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]10
[/TD]
[TD][/TD]
[TD]0.3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]median
[/TD]
[TD]0.55
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

i want to calculate the ratio of two set data..then find the median of that ratios.... if the ratio equal to #div/0!, will ignore it...

how to write a formula to find the median directly?
(instead of calculate the ratio first)


thank you all

 

[lrobbo314]

How about this?

=MEDIAN(IFERROR(A2:A11/B2:B11,0))

To make this work, when entering the formula, hit Control + Shift + Enter to make it an array formula.

 

[jackyuen]

How about this?

=MEDIAN(IFERROR(A2:A11/B2:B11,0))

To make this work, when entering the formula, hit Control + Shift + Enter to make it an array formula.

Hi....i checked ur method...
but the correct Median=0.55...

can u help again?

 

[Andrew Poulsom]

This formula:

=MEDIAN(IF(ISNUMBER(D2:D11),D2:D11))

returns 55.

 

[Aladin Akyurek]

If you want the median without calculating the ratio's explicitly, control+shift+enter, not just enter:

=MEDIAN(IF(ISNUMBER(A2:A11/B2:B11),A2:A11/B2:B11))