libreoffice calc - Sum up to N highest-value out of a series?

I have a spreadsheet where each row is a set of numerical competition scores. Each row can be thought of as a single competitor in the overall competition, and each column is a competition event. Nothing truly unusual there.

Now, I want to take the sum of the N highest value scores out of each row, such that:

• If less than or equal to N scores are provided, sum all listed scores


• If more than N scores are provided, sum the N highest out of the series

How to do that?

In my particular case, N = 5, but I'm hoping that a generic solution exists.

I'd prefer not using a macro for this, but am willing to explore that avenue if it can't be done with a simple formula.

Answer

Use the LARGE function.

=LARGE(Range;1)+LARGE(Range;2)+…+LARGE(Range;N)

or

=SUM(LARGE(Range;1); LARGE(Range;2); …; LARGE(Range;N))

Note that, in some locales, function parameters must be separated by commas rather than semicolons.