Spreadsheet formulas for converting letter grades to percentages and percentages to letter grades
Below are links to spreadsheets containing my standard formulas for converting from letters grades to percentages and vice versa.
I use the first formula (letter grades to percentages) when I am marking work qualitatively (e.g. essays, translations, and other things that are not easily scored numerically), but need a number to use in calculating the final grade; I use the second formula (percent to letter grade) when I am calculating the final grade for submission to the University (the University recrds only letter grades). With some tweaking, you could use this second formula to convert to grade points or to other systems (e.g. First/Second class, and so on).
There are two versions of each formula: a dynamic and a static version. The static version is simply the formula I use in my spreadsheets and it is based on the letter:percentage equivalences defined elsewhere on my website. The dynamic version is built within the spreadsheet using numbers supplied by the user. This has the advantage of being adaptable, but it has the disadvantage of requiring you to copy more cells into your own spread sheet if you want to use it (because it depends on internal references, you need to copy both the formula and the table of equivalences in the stylesheet). In both cases, there are instructions (hopefully clear) on how to use the formulas in your own grade spreadsheets.
Here is the text of the static formula (it assumes its data is coming from cell A32 in the first case and A35 in the second. The easiest way of adapting it to your own uses is to paste the formulas into cells B32 or B35. After you have done that they should automatically change depending on where you place them in the spreadsheet):
=IF(A32="A+",1,IF(A32="A",0.92,IF(A32="A-",0.88, IF(A32="B+",0.84,IF(A32="B",0.8,IF(A32="B-",0.76, IF(A32="C+",0.72,IF(A32="C",0.68,IF(A32="C-",0.64, IF(A32="D+",0.6,IF(A32="D",0.56,IF(A32="F",0.334,A32))))))))))))=IF(A35<0.495,"F",IF(A35<0.575,"D",IF(A35<0.615,"D+", IF(A35<0.655,"C-",IF(A35<0.695,"C",IF(A35<0.735,"C+", IF(A35<0.775,"B-",IF(A35<0.815,"B",IF(A35<0.855,"B+", IF(A35<0.895,"A-",IF(A35<0.935,"A","A+")))))))))))
