Max (and second max) value across five columns

1

Hi,
I need some help find a formula to extra the max value from a row across 5 columns.

Column A, B, C, D, E have values of 10, 8, 7, 6, 5 in row 1 respectively. What formula can return me the max value (and ideally name of column)? How would I get send-highest value?

Max seems to only work within one column.

Thanks, Marlin

2

hi @Marlin_Watling

You have to create a list of values and ask for the max

image
image2875Ă—911 132 KB

and an edit since I saw in the header a second question

image
image2915Ă—1008 148 KB

you can replace the controller in the formula by a direct number like -2 or -3 if that feels easier.

enjoy!
cheers, Christiaan

2 Likes
3

Hi Marlin, you may also consider this solution, similar to Christian’s, but without the need for the control and also returning the name of the columns.

3 Likes
4

hi @Ryan_Martens2 ,

thanks for alternatives. The Nth() version is concise, I like that, easy to read for a human as well :wink:

I went for the bit longer version for this permits you to do a sum (which I see often asked for) or an average like below:

List(thisRow.[Column 2],thisRow.[Column 3],thisRow.[Column 4],thisRow.[Column 5],thisRow.[Column 6],thisRow.[Column 7],thisRow.[Column 8],thisRow.[Column 9]).Sort().Slice(Controller_Range).Average()

anyway good to see these alternatives!
cheers, Christiaan

5

Absolutely wonderful - love it!