Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Adding up "top 4 of 5" scores

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

0
1977
6

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 30, 2023 12:10 AM

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:

- Races (e.g. "March 19, 2023 Race")
- Entries (e.g. "Boat #5"). These roll up to the relevant race.
- 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!

6 Replies 6

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 30, 2023 03:51 AM - edited May 02, 2023 12:46 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 01, 2023 08:16 AM

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))`

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 02, 2023 10:47 AM

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.)

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 07, 2023 11:34 AM - edited May 18, 2023 02:21 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 18, 2023 08:20 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 21, 2023 10:43 PM