Adding up "top 4 of 5" scores

[TessaKohl]

I'm really struggling with a formula which is the final step of my solution. I need a way to sum up only the top 4 values within a list of rolled up values (there may be between 0 and 5 values in the rolled up values array). Is this possible?

The context is I'm calculating winners for a series of 5 rowing races, and we determine a winner by combining their 4 highest point values. My base is structured with the following tables:

1. Races (e.g. "March 19, 2023 Race")
2. Entries (e.g. "Boat #5"). These roll up to the relevant race.
3. Competitors (e.g. "John Smith"). These roll up to the relevant races and entries.

For each entry, there is a points value that is calculated based on the final time, boat type, and average age of competitors. Competitors in the same entry (i.e. boat) all get the same number of points for any given race. At the end of the season, we give awards to the individual competitors with the most points for their top 4 scores of the 5 races. I have this all working except for the part where I need to exclude the lowest score for people who rowed in all 5 races. 

Thanks in advance for any assistance!

[TheTimeSavingCo]

Edit: Please ignore this message and use Hamlin_Krewson2's solution instead, it's a much better and more elegant way of handling this

Hmm, try:
1. In "Competitors", create a rollup field to get their top score with "MAX(values)"
2. In "Entries", create a lookup field to display the value from the field we just created in step 1
3. In "Entries", create a formula field to check whether that entry's score is the same as the value in the lookup field from step 2
4. In "Competitors", create a rollup field to get the second highest score with "MAX(values)" and a conditional to not include records that have the top score by checking against the formula field we created in step 3

And we can repeat this for the rest of the scores we need, does that make sense?

[Hamlin_Krewson2]

Rollups are just formula fields, and as such they can contain almost any formula you can think of. While they don't show IF() in the formula search you can still use it. Assuming (as your post indicates) there will only ever be a max of 5 values in the rollup, this would subtract out the smallest of those 5 only if there are more than 4. 

SUM(values)-if(COUNT(values)>4,MIN(values))

[kuovonne]

The proposal from @TheTimeSavingCo has several problems with it. It uses a lot of helper fields and a lot of back-and-forth rollups which can slow down the base. It will also produce the incorrect result if there are duplicate scores.

@Hamlin_Krewson2 has a right answer. It is very similar to my solution, which I haven't had the time to write up until today.

My version is a little prettier for people without any scores, and it throws in error checking in case someone has more than 5 scores.

IF(
  COUNTALL(values) = 0,
  BLANK(),
IF(
  COUNTALL(values) < 5,
  SUM(values),
IF(
  COUNTALL(values) = 5,
  SUM(values) - MIN(values),
  "Error, too many values" * 1
)))

 
You can see the full write-up here. (Note that this link might change in the future.)

[Paul_L]

Hi @kuovonne

This solution is really helpful and makes it simpler to achieve what I was trying to do last year here.

I've now used rollup fields to calculate the total points rolled up from Results (can also filter the Rollup by a only counting Tournaments for a specific year).

However, I can't figure out how to extend this example to make it work for Best 4 out of 6, or specifically Best 4 out of 7? Feels like I'm missing something simple here to be able to do this... Can this be done with the rollups?

[kuovonne]

I should probably rename my solution to “drop lowest score” because it only drops the lowest score. If you want to drop more scores, you will need a different approach. 

Off the top of my head, I would use automations to keep records sorted in descending order, then use rollup fields so that each record can determine its place and if it should be included in the sum. Then another rollup to sum only the values to be included. 

I don’t have time for a full write up now. I will try to remember later this week. 

[kuovonne]

I did the writeup here in my Guide to Airtable. (You may need to search for it, as deep linking doesn't work well.)