Highest of five columns

[razzy]

Hi All,

Can someone please help me with this. from the attached excel worksheet, i need to output for every row the highest value from the five columns is there a formula i can use or a way to manipulate this using the tools.

[Luke_C]

Hi @razzy 

Here's two ways:

1. Use the MAX() function. This will work if you don't expect any new columns.
2. Transpose the data and use a summarize to find the max for each row, then join it back to the original data.
   1. This will work for any number of new columns added in the future.

[MarqueeCrew]

@razzy ,

a little different approach than @Luke_C 

take your data directly into a summarize with max() on each of the 5 fields.  

use an APPEND FIELDS tool and put those answers into your original data.  

I'm a little sick.  

use a formula tool. 

max([field1],[field2])

 I'm typing this on my iPhone while at doctors.   Put all 5 fields and you'll get that max value. 

cheers,

 mark

[razzy]

Thank you so much for responding promptly ,  how do i retain the column name for the highest value using this formula which works well and easy as an addition column.

[MarqueeCrew]

Hmmm.  What if there is a tie?

[MarqueeCrew]

I've asked my friend @grossal  to expand on:

 create a maxValue field in formula 

then create an if statement that checks the fields in priority order

if field = maxValue then fieldname

 he'll give you a working formula. 

cheers

[razzy]

Yes looking at it i think that what i want can you share a workflow or the formula

Thanks

[grossal · Accepted solution]

Hi Razzy,

thanks for tagging me Mark. I'd probably like / recommend the route that Luke started and say: Try to avoid a formula as it includes manual effort and changes in case you want to scale up.

If we are allowed to avoid a formula, I'd go this route:

It's similar to Luke solution but keeps in mind the newest information that we got from you regarding 'I also want the column name'. We start with a Record-ID to differentiate the value after the pivoting (Transpose-Tool, Key column = RecordID). After that we can simply sort by RecordID (for convenience) and Value - DESCENDING and than use a Sample tool to get the first record per RecordID (Select RecordID as a grouping column). After that we can rejoin our new information to the original (on RecordID) and we are set (I did a little renaming in the join tool to make it look nicer).

In case of a tie, this method will pick the first column name. If you'd like to have "all column that match the highest" than we need to slightly adapt it. But otherwise this method is pretty dynamic and robust for future extension and more / less columns.

Formula only:

If you would really really want to go full manual - what you should probably never do - you do something like this:

I highly recommend to avoid this at any cost.

A compromise: A little formula and a little automated:

I'd still say we should go route 1, but if you want to definitely use the max formula and make at least the part after it dynamic (to avoid to nested IF THEN ELSE over and over again), than you could do something like this:

It's pretty close to the 1st route, but we don't need the sort. Instead we are transposing here on two key column, our RecordID and the Highest-Value and than only use a Filter to find the rows that match the highest value, afterwards I'm using a sample to ensure that we only get one column name, otherwise we'd multiply rows when we have two values that equal the highest.

(I'm sitting in a train with bad WIFI right now, therefore I can't upload you the workflow, but I tried to make the description for route 1 as good as possible)

Best

Alex

[razzy]

THANK YOU SO MUCH