# How to get the average of the higher M values in N cells?

I have a spreadsheet with values in a matrix with several rows. For each row I have a N numbers. I want to calculate the average of the M higher values of the row. With M<N. Is there a common function to do it?

If there is no such function, I suppose I can add it using the python plugins for gnumeric or the basic plugin for libreoffice.

• Try and be a bit more clear on what you are trying to accomplish - perhaps give a small example. Jun 29 '13 at 0:08
• Is not exactly the same question, because in this we don't know previously how many values will use. In the previous question is explicit that the cells number to select is 3. So the function as I need has a set of cells and a number M as parameters. For example maverage(set, m). Jun 29 '13 at 14:10
• Possibly related: Sum up to N highest-value out of a series? (disclaimer: my own question :))
– user
Jun 29 '13 at 20:08

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.