Converting final grades to 4.0 grading scale

[Dzupiris]

Hi all,

I'm new to using Apple's numbers in creating grades for my students. I'm almost done making their grades but I don't know how to convert their raw final grades to the 4.0 grading scale. I just don't know how to make the formula. Can somebody help me? Below is a screenshot of what I've done so far.

Thanks

 

[chabig]

Since there is no numeric conversion formula for your scale, just nested if statements:

 

[svenmany]

If you ignore, for the moment, the top and bottom ranges, you could use

FLOOR(A2,5)÷10 − 4

Then throw in the special cases for the top and bottom ranges, as chabit did. So,

IF(A2< 50,0, IF(A2 > 79, 4, FLOOR(A2,5)÷10 −4))

(I'm sure I'll edit my post once I realized I messed it up. But, for now, give that a shot.)

P.S.

I'm assuming our use of "A2" is understood to be whatever cell is to the left of the calculated value.

 

[Dzupiris]

Thanks guys.

I just tried it but it came up with a syntax error. Here's the screenshot.

Edited: Sorry my bad. The first 'and' was not inside that 'thing' lol. Your formula worked! Thank you so much.

 

[Dzupiris]

Another issue came up. Both 70 but different grade? Here's the screenshot.

 

[chabig]

Another issue came up. Both 70 but different grade? Here's the screenshot.

My guess is that you have a typo in the formula, but I don't see it. Also, taking another at this, I don't know why used the AND formula. It's not necessary. Try this simpler formula (I'm putting it in the text here so you can copy and paste):

IF(A2≥80,4,IF(A2≥75,3.5,IF(A2≥70,3,IF(A2≥65,2.5,IF(A2≥60,2,IF(A2≥55,1.5,IF(A2≥50,1,0)))))))

Another reason you might see the same number return different results is that you don't really have two 70s. The format isn't showing any decimals but you might have some hiding behind those figures. Use the ROUND formula on your final grade column.

ROUND

ROUND(num-to-round, digits) returns a number rounded to the specified number of places.

support.apple.com

 

[Dzupiris]

Ok. Thanks for your help!

I know now what causing it. I rounded their final score to zero decimal. When I rounded it to one decimal, it showed their real score. Here's the screenshot.

 

[svenmany]

As an aside, my formula is less likely to result in a typo. If your resistance to the formula is that it seems like magic, I should explain the simple logic. And, I didn't need to edit my post since I didn't mess up; I tested it.

If you ignore the top and bottom ranges, and only consider the lower bounds of the other ranges, you just have a straight line. The FLOOR just takes a grade to the lower bound of the range the grade is in. (It might be good to learn about that simple function). Then you just have to calculate the slope and some offset. When 50 changes to 55, the grade changes from 1.0 to 1.5; a change of 5 in score corresponds to a change in .5 of grade. So the slope is .1 (that is the divide by 10 that you see). Then just throw in an offset to make it line up.

If that all seems confusing, then chabig's approach is best.