Using only numbers that lie inside some interval

Nikola · June 23, 2020, 4:50pm

Hello,

I want to use from one column only numbers that lie in some interval (say, between 0 and 10). I want to calculate their sum, average, median and whatever else is possible with them. So, numbers from the column that lie outside the interval must be totally ignored.

Is this possible and how?

Sincerely,
Nikola

erAck · June 23, 2020, 5:16pm

You can use an array evaluation of the form

=AVERAGE(IF((0<=A1:A6)*(A1:A6<=10);A1:A6;""))

Close the formula cell input with Shift+Ctrl+Enter instead of just Enter for array mode.

Note this works only with functions that ignore text content elements (returned by the IF() here for non-matching values), like SUM(), AVERAGE(), MEDIAN(), …

LeroyG · June 23, 2020, 5:17pm

LibreOffice Help on SUMIFS function.

LibreOffice Help on COUNTIFS function.

LibreOffice Help on AVERAGEIFS function.

erAck · June 23, 2020, 5:24pm

Yes, but there is no MEDIANIFS() or other …IFS().

Nikola · June 23, 2020, 5:34pm

Thank you very much.

Nikola · June 23, 2020, 5:36pm

By the way, I tried averaging with averageifs, but I was always getting errors. I didn’t find what was wrong.

erAck · June 23, 2020, 5:41pm

For the same example that should be

=AVERAGEIFS(A1:A6;A1:A6;">=0";A1:A6;"<=10")

Nikola · June 24, 2020, 11:26am

Thank you very much.