Median IF range of data

[dstreff]

I am trying to take the median of a range of values, filtered by the number in the limit column.

As small subset of the data is below.

For example, I need to write a function that finds the median of all the values for records with a Limit of 500.

In this subset of data the function should return 2590

I tried using =MEDIAN(IF(F2:F5=500,B2:E5)) and using the Ctrl+Alt+Enter method for arrays, but got the wrong answer.

Any help would be much appreciated.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Record[/TD]
[TD]Value 1[/TD]
[TD]Value2[/TD]
[TD]Value3[/TD]
[TD]Value4[/TD]
[TD]Limit[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1392[/TD]
[TD]1496[/TD]
[TD]2638[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1412[/TD]
[TD]1496[/TD]
[TD]2643[/TD]
[TD][/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1520[/TD]
[TD]1538[/TD]
[TD]2658[/TD]
[TD]3036[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2062[/TD]
[TD]2590[/TD]
[TD]3826[/TD]
[TD]10842[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Its because the blank cells will count as a zero:

=MEDIAN(IF(F2:F5=500,IF(LEN(B2:E5)>0,B2:E5)))

 

[dstreff]

Thank you very much! That worked perfectly