I have a column of data where invalid entries are marked by a certain number (-999 in my case).

If I want to calculate the average of all numbers excluding the invalid (-999) entries, there is the convenient function AVERAGEIF. Now, I want to calculate the **median** of the data excluding the “-999” entries. Unfortunately there is no MEDIANIF and my attempts to combine the MEDIAN function with IF have been unsuccessfull, because IF does not seem to operate on ranges.

How can I write a function that gives me the median of the data and excludes the -999 entries?