Skip to main content
Solved

Find column with highest value in row

  • August 22, 2024
  • 4 replies
  • 45 views
Sareyh
Forum|alt.badge.img+6
  • Sareyh
  • Participating Frequently

I am trying to write a formula to return the name of the column for each rows highest value. Attached is what my table looks like. So for example the first row should return blue, and the second row should return purple. 

Thank you!

Best answer by Dominic11

Hi Sareyh,

You could use MAX() and a bunch of nested IF statements. Not exactly elegant and there is likely a cleverer way, but it should work. Have you considered what you would want to happen if more than one row has the same highest value?

 

IF( {Red} >= MAX({Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Red", IF( {Orange} >= MAX({Red}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Orange", IF( {Yellow} >= MAX({Red}, {Orange}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Yellow", IF( {Green} >= MAX({Red}, {Orange}, {Yellow}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Green", IF( {Blue} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Purple}, {Pink}, {Black/Brown}), "Blue", IF( {Purple} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Pink}, {Black/Brown}), "Purple", IF( {Pink} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Black/Brown}), "Pink", IF( {Black/Brown} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}), "Black/Brown", BLANK() ))))))))

 

1 Attachments

4 replies

Dominic11
Forum|alt.badge.img+11
  • Dominic11
  • Brainy
  • Answer
  • August 22, 2024

Hi Sareyh,

You could use MAX() and a bunch of nested IF statements. Not exactly elegant and there is likely a cleverer way, but it should work. Have you considered what you would want to happen if more than one row has the same highest value?

 

IF( {Red} >= MAX({Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Red", IF( {Orange} >= MAX({Red}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Orange", IF( {Yellow} >= MAX({Red}, {Orange}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Yellow", IF( {Green} >= MAX({Red}, {Orange}, {Yellow}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Green", IF( {Blue} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Purple}, {Pink}, {Black/Brown}), "Blue", IF( {Purple} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Pink}, {Black/Brown}), "Purple", IF( {Pink} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Black/Brown}), "Pink", IF( {Black/Brown} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}), "Black/Brown", BLANK() ))))))))

 

Sareyh
Saravanan_009
Forum|alt.badge.img+17

Assuming you have the following columns:

Blue
Purple
Green

IF(
    AND({Blue} >= {Purple}, {Blue} >= {Green}),
    "Blue",
    IF(
      AND({Purple} >= {Blue}, {Purple} >= {Green}),
      "Purple",
      "Green"
  )
)
Sareyh
Sareyh
Forum|alt.badge.img+6
  • Sareyh
  • Author
  • Participating Frequently
  • August 22, 2024

Hi Sareyh,

You could use MAX() and a bunch of nested IF statements. Not exactly elegant and there is likely a cleverer way, but it should work. Have you considered what you would want to happen if more than one row has the same highest value?

 

IF( {Red} >= MAX({Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Red", IF( {Orange} >= MAX({Red}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Orange", IF( {Yellow} >= MAX({Red}, {Orange}, {Green}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Yellow", IF( {Green} >= MAX({Red}, {Orange}, {Yellow}, {Blue}, {Purple}, {Pink}, {Black/Brown}), "Green", IF( {Blue} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Purple}, {Pink}, {Black/Brown}), "Blue", IF( {Purple} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Pink}, {Black/Brown}), "Purple", IF( {Pink} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Black/Brown}), "Pink", IF( {Black/Brown} >= MAX({Red}, {Orange}, {Yellow}, {Green}, {Blue}, {Purple}, {Pink}), "Black/Brown", BLANK() ))))))))

 


Thank you! Would it be possible to also find the second most common color?

Dominic11
Forum|alt.badge.img+11
  • Dominic11
  • Brainy
  • August 23, 2024

Hi Sareyh,

It is possible to do this using formulas, but it is even more cumbersome than the approach to finding the highest value. I am not sure if you are using a free plan, but if you have access to workspace on Team or above I would suggest you solve this using a script. This approach is far easier to maintain should you need to add or remove colour fields.

An example would be:

// Find the highest & second highest values in your table. You need to add the name of your table here... let table = base.getTable("Your Table Name"); // Load the records in the table let query = await table.selectRecordsAsync(); let records = query.records; // List of colour fields. You can add or remove fields here by following this pattern. let colourFields = ['Red', 'Orange', 'Yellow', 'Green', 'Blue', 'Purple', 'Pink', 'Black/Brown']; // Iterate through each record for (let record of records) { // Get the values of the colour fields for the current record let colourValues = colourFields.map(colour => ({ name: colour, value: record.getCellValue(colour) || 0 })); // Sort the colour values in descending order by value colourValues.sort((a, b) => b.value - a.value); // Get the highest and second-highest colours let highestColour = colourValues[0].name; let secondHighestColour = colourValues[1].name; // Update the record with the highest and second-highest colours await table.updateRecordAsync(record.id, { "Highest": highestColour, "Second Highest": secondHighestColour }); }

 You can add this to a scripting block in the Extensions panel, editing the script to switch "Your Table Name" for the name of the table you are using. This script also assumes you have two fields, one called "Highest" and another called "Second Highest".

Sareyh
Terms & ConditionsAccessibility statement