Kuovonne's Guide to Airtable
Share
Explore
Kuovonne's Guide to Airtable
Specific Solutions

Dropping Lowest Score (top 4 of 5)

⁠
⁠
Tip Jar
⁠
Thank Kuovonne for creating this content!
⁠
⁠
⁠
⁠
⁠

Inspiration

In
this post on the Airtable community forum
⁠
, 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.
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.

Discussion

⁠
image.png
⁠
⁠
Let’s start by using a rollup field that gives the sum of all scores using the SUM(values) formula. ​
image.png
⁠
⁠
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!
⁠
Give Kuovonne a sandwich
⁠
Signup for Coda w/ referral link
⁠
⁠
⁠
Buy from my Gumroad Store
⁠
⁠
⁠
⁠
⁠
⁠
⁠

Gallery
Share
 
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.