Skip to content
This requires a lot of helper fields, which can clutter up the base.The sequence of back-and-forth rollups requires alot of calculations which can slow down the base.If a person has the exact same score in multiple records, this system will produce incorrect results.
Kuovonne's Guide to Airtable
- Pages
Share
Explore
Dropping Lowest Score (top 4 of 5)
Inspiration
In , a user wants to add up only the top 4 of 5 scores for each person. Each person can have from zero to five scores.
A different user proposed using back-and-forth rollups to identify the top score. Then, use more back-and-forth rollups to identify the second highest score, and so on. There are several problems with this proposal.
Discussion
Let’s start by using a rollup field that gives the sum of all scores using the SUM(values) formula.
Next, we identify the lowest score with MIN() and subtract it out.
SUM(values) - MIN(values)
But we only want to subtract out the lowest score if there are 5 scores. We can get the number of scores using COUNTALL(). (Depending on how we want to handle blanks, we could use COUNT() or COUNTA() instead.)
IF(
COUNTALL(values) = 5,
SUM(values) - MIN(values),
SUM(values)
)
But what if someone doesn’t have any scores? It would be kinder to leave the sum blank.
IF(
COUNTALL(values) = 0,
BLANK(),
IF(
COUNTALL(values) = 5,
SUM(values) - MIN(values),
SUM(values)
))
Or what if someone has more than 5 values even though they shouldn’t. Let’s show an error.
IF(
COUNTALL(values) = 0,
BLANK(),
IF(
COUNTALL(values) < 5,
SUM(values),
IF(
COUNTALL(values) = 5,
SUM(values) - MIN(values),
"Error, too many values" * 1
)))
More efficient, correct results (even with duplicate scores), no helper fields, and better error handling.
Thank Kuovonne for creating this content!
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.