0

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.

3
  • 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
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.