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!