Median with condition in Calc

bamse · January 12, 2019, 9:00pm

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?

Lupp · January 12, 2019, 11:44pm

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.

===Edit1 2023-05-29===
The opening angle bracket without a closing one was a typo.
Correction: "<no value>"

bamse · January 13, 2019, 1:18am

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

Lupp · January 13, 2019, 12:19pm

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.

bmst · December 18, 2020, 7:30pm

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

dejfrcr · May 29, 2023, 6:08am

If the world was so simple… My data are all valid numbers, but I need a median based on different criteria in different column - I need separate medians for different kind of operations. Your solution gives #VALUE! for me =MEDIAN(IF(G9:G46=“IS2”;"<no value";DK9:DK46)) I am used to fill empty field “” and it usually work like this =MEDIAN(IF(G9:G46=“IS2”;"";DK9:DK46)) but err is the same. With individual cells the IF work as expected. Where is the catch?
AVERAGEIF, MAXIF and so on work cool but median seem to need a special column for each operation with discrete IFs in every cell.

Lupp · May 29, 2023, 11:51am

I didn’t give an explicit solution for this kind of task. You only entered this thread because of its slightly misleading subject. However, the fact on which my above solution was based, namely that MEDIAN(), like other statistical functions, simply ignores text contents in the main reference area, can also be used for a solution approach to your new question.
This obviously was what you attempted, but you missed to make sure that the conditional inner expression was evaluated “iteratively” for every single value referenced as a data value or as a value defining the outcome of your filter, thus returning an ARRAY of values for which the MEDIAN() function makes sense.
Read about “array evaluation” / “array formulas”.
A proper subject for the above mentioned new question would be “There is no MEDIANIFS() function. How to get the functionality?”
If you decide to post that new question, I may meanwhile have considered a possible answer.
Don’t miss to attach a relevant example document as a .ods file to your question. (And don’t insist on exemplifying data in column Denmark.)