Ask Your Question

Median with condition in Calc

asked 2019-01-12 22:00:26 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-01-13 00:44:36 +0200

Lupp gravatar image

updated 2019-01-13 13:20:36 +0200

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.

edit flag offensive delete link more


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

bamse gravatar imagebamse ( 2019-01-13 02:18:43 +0200 )edit

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.

Lupp gravatar imageLupp ( 2019-01-13 13:19:04 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-12 22:00:26 +0200

Seen: 1,242 times

Last updated: Jan 13 '19