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

Question

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 1

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.

Answer 2

OK, if your data numeric data are in rows on Sheet1, then enter your N value into Sheet2!N1, and enter

=SUM(LARGE(Sheet1!1:1, ROW(INDIRECT("1:"&MIN(COUNT(Sheet1!1:1),N$1)))))

into Sheet2!A1.  (If your data are only in, for example, Columns G through Z, change Sheet1!1:1 to Sheet1!G1:Z1.)  Press Ctrl+Shift+Enter to create an array formula.  Drag down as many rows as you need, and you should be done.  (Note that, in Sheet2!A17, for example, Sheet1!1:1 will have automatically changed to Sheet1!17:17, i.e., the 17th row of Sheet1, because the row number is relative –– not preceded by $.)

Explanation:

• COUNT(Sheet1!1:1) is the number of numbers (scores) in the referenced row (which, as indicated above, might be Row 1, Row 17, or whatever row you’re looking at on Sheet2).  I do this because you said, “not all competitors take part in each event”; I assume that the non-participations are blank, or perhaps a non-numeric string.
• N$1 is your specified N value; the number of scores you want to add.
• MIN() is, of course, minimum.  If N is 5, and a competitor was in only three events, we want to add all three.  If a competitor was in seven events, we want to sum the highest five.
• & is the string concatenation operator in Excel (and, from what I’ve heard, Libre Office Calc is very similar to Excel), so, if the number of scores (MIN(…)) that we’re adding is, say, 5, then "1:"&MIN(…) becomes the string value "1:5".
• INDIRECT("1:5") is the region comprising Row 1 through Row 5, and
• ROW() of that is the array {1,2,3,4,5}.
  This is a trick for creating an array value that’s specified by run-time data.
• LARGE(Sheet1!1:1, {1,2,3,4,5}) is the array { LARGE(Sheet1!1:1,1), LARGE(Sheet1!1:1,2), LARGE(Sheet1!1:1,3), LARGE(Sheet1!1:1,4), LARGE(Sheet1!1:1,5) }, which is the largest (highest) score in the range, the second largest, …, and so on, down to the 5th.

I have assumed that you’re in a locale that uses commas for separators.  If you’re in the land of semicolons, do as the semicolons do.

Answer 3

I would approach this by building the scores as a table then keep the table sorted by the score. Then you can look at only the first n columns...

If you need to change n "on the fly" you can do indirect referencing of the cells based on a value that you set.