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

[jackyuen]

A	B		ratio
17	14		1.21
6	11		0.55
5	16		0.31
5	18		0.28
3	0		#div/0!
12	17		0.71
0	12		-
12	8		1.5
3	10		0.3
4	2		2
		median	0.55

<tbody>
</tbody>

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))