Kuovonne's Guide to Airtable

Explore

Kuovonne's Guide to Airtable

Kuovonne's Guide to Airtable

Specific Solutions

Dropping Lowest Score (top 4 of 5)

⁠

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

⁠

Inspiration

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

⁠

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! ⁠ Give Kuovonne a sandwich ⁠ Signup for Coda w/ referral link ⁠ ⁠ ⁠Buy from my Gumroad Store ⁠ ⁠ ⁠ ⁠ ⁠

⁠

Gallery

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.