Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.
Sep 20, 2022 09:07 PM
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:
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.
Sep 20, 2022 09:10 PM
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