Need some help on how to start this problem please

[Irfaan_Sorathia]

Hi, I have a (very) basic understanding of spreadsheets and databases, and usually rely on googling to find formulas to use in spreadsheets and the same for setting up AirTable.

However, I am stuck on the best way to even start to set this up. Or what to search for to get in the right area. Any help would be greatly appreciated.

I am looking to calculate an Honour Roll average for students, keeping in mind that not all students will take the same number/type of classes. Here is how their marks are to be calculated:

1. Using their English and Social Studies marks
2. Using their top Math mark (only grade 12s may have a second math mark if they took Calculus)
3. Using their top Science course mark (this could range from a min of 1 course to 3 or 4)
4. After the above 4 marks have been taken, the top mark from the remaining (from other elective classes, and any other science or math class they have taken) is used.

Those 5 marks are used to calculate an average.

My current thoughts would be to set up a table with the student list, and link each course mark to the student in a different table. Short of manually sorting I don’t know how to make this process efficient.

Thanks in advance for any pointers.

[TheTimeSavingCo]

Hi @Irfaan_Sorathia, if you could provide some example marks that would be super helpful!

I ended up trying to see if I could come up with something for this anyway and the trickiest bit to me seems to be handling possible duplicate marks

For example, if a student is taking two Math courses and scores a “10” for both, then one of the "10"s will be used as their top Math course mark, while their second “10” will need to be part of the calculations to figure out the top mark from the remaining pool of other Math, Science and Elective courses

Here’s what I’ve figured out:

To get the highest Math score is easy enough:

IF(
  {Math 1} > {Math 2},
  {Math 1},
  {Math 2}
)

And getting the second highest math score for the “Remaining Calculation” would be:

IF(
  AND(
    {Highest math score} = {Math 1},
    {Highest math score} = {Math 2}
  ),
  {Math 1},
  IF(
    {Math 1} = {Highest math score},
    {Math 2},
    {Math 1}
  )
)

Explanation: If both of the Math scores are equal, then we’ll just display one of them since they’re the same. And if they’re not the same, we’ll check whether the first math score is equal to the highest math score, and if it is, we’ll show the second one, if not we’ll show the first one, resulting in this:

And you’ll basically repeat this logic for all of the science courses to get the highest and second highest Science mark, and finally repeat this logic once more to compare all the electives against the second highest Math and Science marks

If you don’t want to deal with this you can also just hire me to set it up for you too!

---

While this works, it seems…fairly complicated for something that should be pretty simple to handle, and perhaps someone else might have a more elegant way to handle this

I briefly considered using a script for this as well which we could run via an automation / button, but figured handling it all via formulas might be cleaner