Find the highest 5 values

elifuyar · ‎Dec 27, 2023

Hello everyone. I am new to the Airtable world and I have a question that I would like your help with.

I have 17 number fields. They are called A, B, C, D, E ...

I want to have 5 fields called max1, max2, max3, max4 and max5. Max1 field is going to be the highest value of those 17 fields. Max2 is going to be second highest value of 17 fields and so on till max5. (They don't have to be unique.)

I could find the highest value with the formula MAX(A,B,C,D,...) but couldn't find any info on how to find the other max values.

Example:

A	B	C	D	E	F	G	MAX1	MAX2	MAX3
0	1.31	6.85	0	3.4	5.3	8.5	8.5	6.85	5.3
1	6.2	7.2	2.3	0	0	4.4	7.2	6.2	4.4

I am not even sure if it is possible to do what I'm asking. But I hope I can get some guidance.

kuovonne · ‎Dec 27, 2023

Identifying the top 5 values out of 17 values would be really difficult with formulas. I’ve written some really complex formulas, and while I might try to find the top two or three with formulas, I wouldn’t both to find the top five because things get crazy complicated.

I’m guessing that you come from a spreadsheet background versus a database background, so my suggestion might seem weird to you.

I would take a different approach and move all the scores to a second linked table with a new row for each score. So each row in your main table would have 17 linked records. This secondary table would have four fields: a linked record field to the main table, a numeric field for the score, an text field for the label the score is associated with, and a numeric ranking field. This numeric ranking field would need to be entered manually, calculated with a script, or calculated using an extension. The highest score would have a rank of 1, etc.

Then back in the main table, have conditional rollup fields that roll up the score based on the ranking. For example, the Max1 field would rollup the score field where the score field has a rank of 1.

What you want to do with the top five scores might also have an impact on how to build the system. For example, if you only need to find the sum of the top scores and not the individual scores, you would need fewer rollups. Sorting the linked record field also opens up variations on this method.

elifuyar · ‎Dec 29, 2023

Will this work if I have hundreds of records?

Martin_Hopkins · ‎Oct 13, 2024

I used chatgpt to create this formula. returns the second highest value from 12 columns labelled M01 to M12. It ignores null or blank cells. It works.
MAX( IF(AND({M01}, {M01} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M01}), IF(AND({M02}, {M02} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M02}), IF(AND({M03}, {M03} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M03}), IF(AND({M04}, {M04} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M04}), IF(AND({M05}, {M05} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M05}), IF(AND({M06}, {M06} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M06}), IF(AND({M07}, {M07} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M07}), IF(AND({M08}, {M08} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M08}), IF(AND({M09}, {M09} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M09}), IF(AND({M10}, {M10} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M10}), IF(AND({M11}, {M11} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M11}), IF(AND({M12}, {M12} < MAX({M01}, {M02}, {M03}, {M04}, {M05}, {M06}, {M07}, {M08}, {M09}, {M10}, {M11}, {M12})), {M12}) )