Excel formulas for making grading easier

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))

Comments »

The URI to TrackBack this entry is: http://cephalophory.blogsome.com/2005/12/19/excel-formulas-for-making-grading-easier/trackback/

No comments yet.

RSS feed for comments on this post.

Leave a comment

Line and paragraph breaks automatic, e-mail address never displayed, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>