Median with condition in Calc

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?

Oh my! Will this kind of “good ideas” never end? Use “no value” as indicator for no value. Functions only accepting numbers will then report the due error, and functions ignoring texts will work as expected.

Well meanwhile you may use something like {=MEDIAN(IF(A2:A21=-999;"<no value";A2:A21))} entered with Ctrl+Shift+Enter for array-evaluation. (Do not enter te curly brackets!)

===Edit1 2019-01-13 13:21 CET===
The attachment announced in comment#2 below.

Thanks, that worked. The “good idea” was not from me, because those were external data where the -999 was already there.

If you not are forced to stick to the representation of the data you got them in, you can easily replace the -999 by a clarifying text using the F&R tool. This done, all the accumulating and statistical functions made for numerical data will ignore the respective cells without further measures.
See attachment to the amendment to my answer.

I wanted to upvote your comment, but LibreOffice decided I don’t have enough karma :). Thank you Lupp.