Skip to main content
Solved

Lookup final grade field after comparing range

  • May 21, 2020
  • 3 replies
  • 41 views
M

Hello all,

I have question regarding a formula for the marking sheet for my university module:

I want to compare my students’ final numerical grade (titled ‘total_grade_point’ field in image one) with a range in an adjacent conversion table. The second table contains a numerical range (image two), and I want to get the final string for my student’s grade.

Basically, if you follow the images, I want to contrast my students’ grade point with the ranges in the conversion table, and then yield the grade string (zero, low fail, etc.).

I hope this is somewhat clear.

Thank you all for your help! :pray:

Best,

Manuel

Best answer by Manuel_Angel_Ma

@ScottWorld, you are an absolute hero. Thank you for encouraging me to figure it out on my own too, I learned something about nesting IF statements now. This is how I solved it (used the python markup language in Sublime Text to help me out with the colours):

IF(total_grade_point <= 0.4,
“Zero”,
IF(total_grade_point <= 1.4,
“Low Fail”,
IF(total_grade_point <= 2.4,
“Mid Fail”,
IF(total_grade_point <= 3.4,
“Marginal Fail”,
IF(total_grade_point <= 4.4,
“Low 3rd”,
IF(total_grade_point <= 5.4,
“Mid 3rd”,
IF(total_grade_point <= 6.4,
“High 3rd”,
IF(total_grade_point <= 7.4,
“Low 2.2”,
IF(total_grade_point <= 8.4,
“Mid 2.2”,
IF(total_grade_point <= 9.4,
“High 2.2”,
IF(total_grade_point <= 10.4,
“Low 2.1”,
IF(total_grade_point <= 11.4,
“Mid 2.1”,
IF(total_grade_point <= 12.4,
“High 2.1”,
IF(total_grade_point <= 13.4,
“Low 1st”,
IF(total_grade_point <= 14.4,
“Mid 1st”,
IF(total_grade_point <= 15.4,
“High 2st”,
IF(total_grade_point <= 16.0,
“Exceptional 1st”
))))))))))))))))

3 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • May 21, 2020

This is a great example of “automatic linking“ between tables, which is not possible in Airtable. Airtable only supports “manual linking” between tables.

You would either need to manually link between tables (by linking each Marking record to a Conversion record), or write a JavaScript to manually link each record for you.

However, probably a much better solution for you would be to simply create a new formula field in your Markings table which would yield the result that you’re looking for. The Conversions table would be unnecessary. You could still keep the Conversions table as a personal reference to yourself, but you would translate all the logic of that table into a single formula field within your Markings table.

I’m not at an actual computer right now, but when I’m back at my computer later, I could help you construct the formula if you can’t figure it out on your own.

M
  • Manuel_Angel_Ma
  • Author
  • New Participant
  • Answer
  • May 21, 2020

@ScottWorld, you are an absolute hero. Thank you for encouraging me to figure it out on my own too, I learned something about nesting IF statements now. This is how I solved it (used the python markup language in Sublime Text to help me out with the colours):

IF(total_grade_point <= 0.4,
“Zero”,
IF(total_grade_point <= 1.4,
“Low Fail”,
IF(total_grade_point <= 2.4,
“Mid Fail”,
IF(total_grade_point <= 3.4,
“Marginal Fail”,
IF(total_grade_point <= 4.4,
“Low 3rd”,
IF(total_grade_point <= 5.4,
“Mid 3rd”,
IF(total_grade_point <= 6.4,
“High 3rd”,
IF(total_grade_point <= 7.4,
“Low 2.2”,
IF(total_grade_point <= 8.4,
“Mid 2.2”,
IF(total_grade_point <= 9.4,
“High 2.2”,
IF(total_grade_point <= 10.4,
“Low 2.1”,
IF(total_grade_point <= 11.4,
“Mid 2.1”,
IF(total_grade_point <= 12.4,
“High 2.1”,
IF(total_grade_point <= 13.4,
“Low 1st”,
IF(total_grade_point <= 14.4,
“Mid 1st”,
IF(total_grade_point <= 15.4,
“High 2st”,
IF(total_grade_point <= 16.0,
“Exceptional 1st”
))))))))))))))))

ScottWorld
Justin_Barrett
ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • May 21, 2020

@ScottWorld, you are an absolute hero. Thank you for encouraging me to figure it out on my own too, I learned something about nesting IF statements now. This is how I solved it (used the python markup language in Sublime Text to help me out with the colours):

IF(total_grade_point <= 0.4,
“Zero”,
IF(total_grade_point <= 1.4,
“Low Fail”,
IF(total_grade_point <= 2.4,
“Mid Fail”,
IF(total_grade_point <= 3.4,
“Marginal Fail”,
IF(total_grade_point <= 4.4,
“Low 3rd”,
IF(total_grade_point <= 5.4,
“Mid 3rd”,
IF(total_grade_point <= 6.4,
“High 3rd”,
IF(total_grade_point <= 7.4,
“Low 2.2”,
IF(total_grade_point <= 8.4,
“Mid 2.2”,
IF(total_grade_point <= 9.4,
“High 2.2”,
IF(total_grade_point <= 10.4,
“Low 2.1”,
IF(total_grade_point <= 11.4,
“Mid 2.1”,
IF(total_grade_point <= 12.4,
“High 2.1”,
IF(total_grade_point <= 13.4,
“Low 1st”,
IF(total_grade_point <= 14.4,
“Mid 1st”,
IF(total_grade_point <= 15.4,
“High 2st”,
IF(total_grade_point <= 16.0,
“Exceptional 1st”
))))))))))))))))


Haha, you’re welcome! :slightly_smiling_face:

And that is so fantastic that you figured out this Nested IF formula on your own!! Congratulations! :slightly_smiling_face:

M
Terms & ConditionsAccessibility statement