Excel formulas for making grading easier
FILED UNDER Uncategorized
You can paste the following into an Excel spreadsheet to make the conversions indicated. However, you’ll have to fix the quotation marks to make them straight instead of curly and remove the extra line breaks.
To convert a four-point grade to a letter grade, without rounding off to the nearest grade (a number like 3.17 will yield “B??”):
=CONCATENATE(CHAR(69+1/3-Jfour-point),IF(INT(Jfour-point)<>
Jfour-point,IF(INT(Jfour-point*3)=Jfour-point*3,
CHAR((Jfour-point-INT(Jfour-point))*6+41),”??”),”"))
To convert a four-point grade with rounding:
=CONCATENATE(CHAR(69-ROUND(Jfour-point,0)),
IF(ABS(Jfour-point-ROUND(Jfour-point,0))<0.17,"",
IF(ROUND(Jfour-point-INT(Jfour-point),1)=0.5,"-/"
&CHAR(70-ROUND(Jfour-point,0))&"+",
CHAR(41+2*(ROUND((Jfour-point-INT(Jfour-point))*3,0))))))
To convert a percentage into a four-point scale (e.g., 85% becomes 3.0, or B)
=(((Jpercent*0.3)-16)+(IF((Jpercent*0.3)-16=14,-1,0)+
(IF((Jpercent*0.3)-16>11,-1,0))))/3
To convert a letter into a four-point scale (e.g., B- becomes 2.67):
=69-CODE(LEFT(Jletterl,1))+IF(LEN(Jletterl)>1,
(44-CODE(RIGHT(Jletterl,1)))/3,0)
To convert a letter grade that may incorporate a slash (like B/B+ for example) or may not into a four-point scale:
=AVERAGE(69-CODE(LEFT(IF(ISNUMBER(SEARCH("/",Jslash)),
LEFT(Jslash,SEARCH("/",Jslash)-1),Jslash),1))+
IF(LEN(IF(ISNUMBER(SEARCH("/",Jslash)),
LEFT(Jslash,SEARCH("/",Jslash)-1),Jslash))>1,
(44-CODE(RIGHT(IF(ISNUMBER(SEARCH("/",Jslash)),
LEFT(Jslash,SEARCH("/",Jslash)-1),Jslash),1)))/3,0),
69-CODE(LEFT(IF(ISNUMBER(SEARCH("/",Jslash)),
RIGHT(Jslash,LEN(Jslash)-SEARCH("/",Jslash)),Jslash),1))+
IF(LEN(IF(ISNUMBER(SEARCH("/",Jslash)),
RIGHT(Jslash,LEN(Jslash)-SEARCH("/",Jslash)),Jslash))>1,
(44-CODE(RIGHT(IF(ISNUMBER(SEARCH("/",Jslash)),
RIGHT(Jslash,LEN(Jslash)-SEARCH("/",Jslash)),Jslash),1)))/3,0))
