Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Find column with highest value in row

Solved
Jump to Solution
597 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Sareyh
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
Dominic
6 - Interface Innovator
6 - Interface Innovator

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

Screenshot 2024-08-22 at 10.38.00.png

See Solution in Thread

4 Replies 4
Dominic
6 - Interface Innovator
6 - Interface Innovator

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

Screenshot 2024-08-22 at 10.38.00.png

Saravanan_009
8 - Airtable Astronomer
8 - Airtable Astronomer

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Dominic
6 - Interface Innovator
6 - Interface Innovator

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

Screenshot 2024-08-23 at 19.50.49.png