median of cells if conditions - openoffice-calc

Question

Is there a function in openoffice-calc like AVERAGEIFS for the median?

If not, is there a function that gives the list of all the cells satisfaying a condition, so that I can apply the median to that list?

I need to convert this formula:

=AVERAGEIFS(J$22:J$720,D$22:D$720,">=0",D$22:D$720,"<2")

into something like:

=MEDIANIFS(J$22:J$720,D$22:D$720,">=0",D$22:D$720,"<2")

Answer 1

Enter the following as an array formula by pressing CtrlShiftEnter.

=MEDIAN(IF(D22:D720<0;"<no value";IF(D22:D720>2;"<no value";J22:J720)))

This will find the median for an odd number of values, or the mean if there is an even number.

Adapted from https://ask.libreoffice.org/t/median-with-condition-in-calc/38522.