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.