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.
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.
& 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, andROW() of that is the array {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17}.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.
