OK, if your data numeric data are in rows on Sheet1
, then enter your M value into Sheet2!M1
, and enter
=AVERAGE(LARGE(Sheet1!1:1, ROW(INDIRECT("1:"&M$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.
Explanation:
&
is the string concatenation operator in Excel (and, from what I’ve heard, Libre Office Calc is very similar to Excel), so, if Sheet2!M1
is, say, 17, then "1:"&M$1
becomes the string value "1:17"
.
INDIRECT("1:17")
is the region comprising Row 1
through Row 17
, and
ROW()
of that is the array {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}
.
This is a trick for creating an array value that’s specified by run-time data.
LARGE(Sheet1!1:1, {1…17})
is the array { LARGE(Sheet1!1:1,1), LARGE(Sheet1!1:1,2), LARGE(Sheet1!1:1,3), …}
, which is the largest member of Sheet1!1:1
, the second largest, …, and so on, down to the 17th.
Thanks to teylyn, whose answer to the similar question I used for inspiration.