# How to calculate quartiles of non-zero's

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?

Best regards
Marvin

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.

Thank you for the hint! I fixed the tags.

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.

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

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.