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.

Record	Value 1	Value2	Value3	Value4	Limit
1	1392	1496	2638		500
2	1412	1496	2643		1000
3	1520	1538	2658	3036	500
4	2062	2590	3826	10842	500

<tbody>
</tbody>

 

[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