Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 21, 2024 10:13 PM
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!
Solved! Go to Solution.
Aug 22, 2024 01:44 AM
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()
))))))))
Aug 22, 2024 01:44 AM
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()
))))))))
Aug 22, 2024 01:14 PM
Assuming you have the following columns:
Blue
Purple
Green
IF(
AND({Blue} >= {Purple}, {Blue} >= {Green}),
"Blue",
IF(
AND({Purple} >= {Blue}, {Purple} >= {Green}),
"Purple",
"Green"
)
)
Aug 22, 2024 03:48 PM
Thank you! Would it be possible to also find the second most common color?
Aug 23, 2024 10:55 AM
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".