Median using if/and statement

[mark91345]

I just can't figure out why my median formula isn't working. I am trying to get the median annual return, based on "groupings" of distances. For example, since A2:A5 are <= 50 miles and are also > 0, I want Excel to give me the median annual return for B2:B5 (which is 0.27).
Help!

Excel 2012

	A	B	C	D	E
1	Distance in Miles	Annual Return		DISTANCES GROUPED	MEDIAN ANNUAL RETURN
2	1	0.15		0
3	22	0.33		50	0.00000
4	48	0.33		100
5	49	0.21		150
6	81	-0.11		200
7	95	0.71		250
8	125	0.88		300
9	128	0.06
10	138	-0.03
11	155	1.67
12	156	0.14
13	168	0.40
14	173	0.03
15	186	0.26
16	192	0.38
17	194	0.29
18	265	0.48
19	275	0.20
20	288	0.29
21	297	0.91

<tbody>
</tbody>

forMrExcel​

 

[AliGW]

What is the formula you are using?

 

[mark91345]

=MEDIAN(IF(AND($A$2:$A$21>D2,$A$2:$A$21<=D3),$B$2:$B$21))

<colgroup><col width="453"></colgroup><tbody>
</tbody>

(Sorry about that! Don't know why it didn't copy over the first time).

What is the formula you are using?

 

[lexxie2013]

You could try the array formula (Ctrl + Shift + Enter)

=MEDIAN(IF((A2:A21>D2)*(A2:A21<=D3),B2:B21))

 

[mark91345]

Thank you! This worked perfectly.

You could try the array formula (Ctrl + Shift + Enter)

=MEDIAN(IF((A2:A21>D2)*(A2:A21<=D3),B2:B21))