How to calculate quartiles of non-zero's

Picanto2020 · July 18, 2021, 3:11pm

Dear community,

I’m currently working on a 37x37 matrix filled with values between 0 and about 250. Most of these values are = 0, but I’m interested in calculating the first and third quartiles + the median of the non-zero values.
My approach was to include the IF-Function in the argument like this: =QUARTILE(IF(C9:AM45>0;C9:AM45); 1) where C9:AM45 is my matrix and 1 stands for the first quartile. Unfortunately it only returns “#value!”.

Has anyone suggestions on how to correctly apply this condition of >0 in the quartile-function?
Many thanks in advance!

Best regards
Marvin

ajlittoz · July 18, 2021, 3:21pm

Your question is not related at all with the mathematical formula editor (the Math application of the suite). So please, retag to erase math. This triggers erroneous alerts on listeners.

Picanto2020 · July 18, 2021, 3:24pm

Thank you for the hint! I fixed the tags.

anon73440385 · July 18, 2021, 3:45pm

Hello,

IF() is not an array formula, hence you need to force IF() to evaluate array element by array element and this way return an array to function QUARTILE(). Try

=SUMPRODUCT(QUARTILE(IF(C9:AM45>0;C9:AM45);1)) –or–
=SUMPRODUCT(QUARTILE(IF(C9:AM45=0;"";C9:AM45);1))

(function SUMPRODUCT() forces array evaluation). You could also use:

=QUARTILE(IF(C9:AM45>0;C9:AM45);1) but then you need to type CTRL+SHIFT+ENTER (instead of ENTER only) to finalize the formula, make it an array formula and to get {=QUARTILE(IF(C9:AM45>0;C9:AM45);1)} (watch the error message “Wrong data type” in the status bar, if not doing so).

Hope that helps.

Picanto2020 · July 19, 2021, 3:44pm

Thank you very much for your explanation and help!
It worked with both SUMPRODUCT-lines but also with the CTRL+SHIFT+ENTER approach. Now I understand the problem in the first place.

Best regards
Marvin

Lupp · July 19, 2021, 7:33pm

I would dissuade from using IF(C9:AM45>0;C9:AM45). IF() expressions without an explicit Else-part are specified to return False as the default alternative, and False reformatted to an ordinary NumberFormat is zero (0). A surrounding expression expected to ignore the False elements must therefore evaluate the format. That’s against the principle that changing formats never should change values (or be expected to do so).
Using the mentioned expression as a “standalone” array expression with output to the respective cell range, and applying the QUARTILE() function to that data range would actually return different results (Tested with LibO V 7.2.0.1RC). We shoudn’t rely on implementations supporting sloppiness in such a confusing way.
BTW An alternative to exclude zeroes by replacing them with (empty) text would be
IFERROR(1/(1/C9:AM45);"") (under array-evaluation). It may look strange but is expected to be rather efficient, with an expression in place of the reference.