Calc: Conditional/Filtered Median based on various conditions

[Nekkowe]

I am helping out with a game tournament that allows spectators to “bet” what amounts to monopoly money on the matches (think Blaseball).

To keep track of the bets, I have one large table where each row contains a bet with its amount, match ID, the spectator placing it etc.

For the sake of fun statistics, I want to find the MEDIAN() of the bets for each individual match, as well as for each round, each bettor etc. independently.

My attempts that have failed so far:

• For things like MIN, MAX, SUM and AVERAGE there are MINIFS, MAXIFS, SUMIFS and AVERAGIFS, but MEDIAN has no such built-in function.
• I have tried to “filter” the results with MEDIAN(IF([condition], [range]), [some other value MEDIAN will ignore]), but I have been unable to find any values that MEDIAN will actually “ignore” (the way the aforementioned functions ignore e.g. text values). It will throw an error if passed strings, even empty ones. It will interpret FALSE as a 0 and skew the results, return #N/A if any input value is #N/A, etc.
• I have tried to find a way to simply remove non-matching values from the array based on the condition, but Excel’s FILTER() function seems to not have any equivalent within Calc. (@Lupp, there’s one example of a useful newer formula addition for you.)

How can I solve this problem?

[Lupp]

@Nekkowe

but Excel’s FILTER() function seems to not have any equivalent within Calc. (@Lupp, there’s one example of a useful newer formula addition for you.)

See tdf#126573. You remember: Feature request go to the bug tracker.
Concerning the linked request: I won’t explicitly object against the implementation. However there are lots of disadvantages to be expected if users send the results directly to cell ranges instead of using them in subexpressions. There are more.
Anyway: Workarounds or UDF based solutions are sometimes more complicated than in the case of SEQUENCE().

[Villeroy]

A pivot table calculating sums, counts, averages, medians for each month, person and product.
Pivot_Month_Person_Product.ods (94.7 KB)

[Lupp]

@Nekkowe

I have tried to “filter” the results with MEDIAN(IF(, ), )

1. The IF() expression must be complete like IF(C1:C10>5;C1:C10;"§"). Then the texts can be ignored as you expected if you regard point 3.
2. The second parameter of MEDIAN() would be eavluated in exactly the same way as the first one. It doesn’t depend on the condition as probably assumed in your example.
3. The parameters of MEDIAN() are evaluated as NumberSequenceLists. Like those of SUM() and MIN() they can’t be specified ForceArray therefore. If you want array-evaluation you need to order it explicitly by entering the formula with Ctrl+Shift+Enter.

The question was edited meanwhile. The quoted part was removed/changed this way.

[Lupp]

You may also be interested in this thread.

[Nekkowe]

Thank you!

I’d put some of what I was writing in < brackets like this, but I think the editor interpreted them as HTML tags and discarded them. I’ll fix it in the post in a second.

You’re absolutely right - I thought it only worked for the other functions mentioned, but not for MEDIAN - the documentation (Documentation/Calc Functions/MEDIAN - The Document Foundation Wiki) sounded like if there’s any text in there at all, it would return Err:504 instead of just ignoring the value. When I checked it for myself in the sheet, I must’ve either forgotten the array formula or just plain entered it wrong.

Thank you for the point about the array formula, as well!