Find column with highest value in row

[Sareyh]

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!

[Dominic · Accepted solution]

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()
))))))))

 

[Saravanan_009]

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]

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

[Dominic]

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".