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.