Search code examples
libreoffice-calc

How to build "argmax" function in libreoffice

I wonder, how I can build what is done in an argmax function in libreoffice. I guess there must be a way without using basic.

Let me show you, what I mean with an example. If I have a table with the following data for the months Jan - Apr. How can I calculate the column displayed as maximum, so that I get the values for the fruits and the total which are in the column in which the total reaches the maximum value (here 16), so everything displayed below maximum should be calculated by that function.

                            maximum
        Jan Feb Mar Apr     Mar
apples  -5  3   7   1       7
oranges 2   5   2   5       2
bananas 17  2   7   9       7
total   14  10  16  15      16

I tried to use MAX to get the maximum value of total and then use a VLOOKUP but it seems only to work if the data is vertically aligned and my real data is not integer data, so I'm not sure if I am guaranteed to get a result because of rounding issues etc.

Solution

  • The first step is really to find the MAX() function for the Totals row.

    Then, using MATCH() function, determine how far from the first column with names the found maximum is.

    And at the very end, using the OFFSET() function, get the entire column from the desired place - move from the heading column to the found maximum position.

    {=OFFSET($A$2:$A$6;0;MATCH(MAX($A$6:$E$6);$A$6:$E$6;0)-1)}

    Array formula OFFSET()