help with calculation in conditional formatting
I hope there is someone out there with a bit more mathematical knowledge than me, who can help me with a calculation I want to do.
I have a form with student's level reports and I need some number fields to automatically change a certain colour, depending on how their actual level compares to the required level. (Example: if their required level is 5, but they have a 4, then it would be orange; if they had a level 2, then it would be red....).
Levels are from 1-10 (10 being the highest) There are various level requirements.
There are 5 conditions.
Red - far below required level (more than 1.51 in the negative)
orange - below level required (up to 1.50 points below level required)
yellow - meets level (the same number as level required)
green - above (up to 1.5 points)
dark green - far above level required (1.51 up to the maximum of 10)
-1.51 or more up to -1.5 0 (same) up to +1.5 from +1.5 1
I hope this is clear enough.
Thanks in advance for any help you can give me.