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.

Daniel Hernández
  • 103
  • 2
  • Try and be a bit more clear on what you are trying to accomplish - perhaps give a small example. – peanut_butter Jun 29 '13 at 00: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). – Daniel Hernández Jun 29 '13 at 14:10
  • Possibly related: [Sum up to N highest-value out of a series?](http://superuser.com/q/571915/53590) (disclaimer: my own question :)) – user Jun 29 '13 at 20:08

1 Answers1

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.

Community
  • 1
Scott - Слава Україні
  • 21,399
  • 46
  • 64
  • 121