Converting a letter grade to a numerical value

[confused teacher]

I am a teacher trying to figure out how to assign a numerical value to a
letter grade. I have created the gradebook for this but I am stuck with the
formulas (or even if it is possible to do this). I have the formula to assign
a letter grade based on the numeric values entered [the formula I am using
is:
=IF(O7>84.5%,"HD",IF(O7>74.5%,"D",IF(O7>64.5%,"C",IF(O7>49.5%,"P",IF(O7>39.5%,"PC",IF(O7>=0%,"F"))))))]
Because of the complexity of the assessment criteria sheet it is easier to
assign a letter grade than a numeric value, however to get the final grade I
need a numeric value.

Based on the following is it possible to create a formula for Excel 2003
that will assign a number to a cell when a letter is entered in another cell.

eg. if a HD+ = 5 points, HD = 4.5 points, HD = 4 points etc and I enter HD+
in say cell A2 and want the numeric grade to appear in cell F2 what formula
would I need to enter in cell F2 to get the numeric grade so that it could be
added with other numeric grades in the criterion to award a final grade for
the criterion. Some criteria have 3 sub criteria and a student might get a
HD, C and P- and the final grade would be the result of the three numeric
values added together and then included with the other criteria in other
sections.

Also is there a formula that can be used to only assign a final grade to a
cell in the event that all other relevent cells have a grade assigned in
them? for example if a student fails to submit and assessment item I do not
want to assign a final grade. Can Excel 2003 stop the final grade cell being
completed based on a formula in the cell?

Thank you very much for your help with this.

 

[Kim]

Hi Confused Teacher,

I'm not an expert but to get around this easier, I would create another
sheet and have all the letter grades in column A and the number grades next
to them in column B.

Then I would do a vlookup formula:

write this formula in the sheet where all your original data is.

vlookup(a:a,sheet1!a:b,2,false)

a:a being the column of letter grades.
sheet1 being the name of the new sheet you create, as mentioned above.

Hope this helps

a:a being the column

 

[Greg Wilson]

The following was based on my interpretation with some guessing. The appended
formula will return 5 if cell A2 has "HD+" in it, 4.5 if it has "HD", 3.5 if
it has "D" in it etc. Change the values in the curly brackets to suit. The
formula will return blank if there is no entry or no match. Enter it in cell
F2 and drag it down to the required number of cells (it will reference A3,
A4, A5 etc.).

=IF(ISNA(VLOOKUP(A2,
{"HD+",5;"HD",4.5;"D",3.5;"C",2.5;"P",1.5;"PC",0.5;"F",0}, 2, FALSE)), "",
VLOOKUP(A2, {"HD+",5;"HD",4.5;"D",3.5;"C",2.5;"P",1.5;"PC",0.5;"F",0}, 2,
FALSE))

The next formula assumes that cells F2:F5 contain the above formula and
return numeric values or blank. Use it to sum the results in F2:F5. The
formula will also return blank if there arn't 4 results. Change the range
reference and target number (4) to suit.

IF(COUNT(F2:F5)<4,"",SUM(F2:F5))

Regards,
Greg

 

[confused teacher]

Thank you very much for this Greg, the formula works well.

Brian

The following was based on my interpretation with some guessing. The appended
formula will return 5 if cell A2 has "HD+" in it, 4.5 if it has "HD", 3.5 if
it has "D" in it etc. Change the values in the curly brackets to suit. The
formula will return blank if there is no entry or no match. Enter it in cell
F2 and drag it down to the required number of cells (it will reference A3,
A4, A5 etc.).

=IF(ISNA(VLOOKUP(A2,
{"HD+",5;"HD",4.5;"D",3.5;"C",2.5;"P",1.5;"PC",0.5;"F",0}, 2, FALSE)), "",
VLOOKUP(A2, {"HD+",5;"HD",4.5;"D",3.5;"C",2.5;"P",1.5;"PC",0.5;"F",0}, 2,
FALSE))

The next formula assumes that cells F2:F5 contain the above formula and
return numeric values or blank. Use it to sum the results in F2:F5. The
formula will also return blank if there arn't 4 results. Change the range
reference and target number (4) to suit.

IF(COUNT(F2:F5)<4,"",SUM(F2:F5))

Regards,
Greg

I am a teacher trying to figure out how to assign a numerical value to a
letter grade. I have created the gradebook for this but I am stuck with the
formulas (or even if it is possible to do this). I have the formula to assign
a letter grade based on the numeric values entered [the formula I am using
is:
=IF(O7>84.5%,"HD",IF(O7>74.5%,"D",IF(O7>64.5%,"C",IF(O7>49.5%,"P",IF(O7>39.5%,"PC",IF(O7>=0%,"F"))))))]
Because of the complexity of the assessment criteria sheet it is easier to
assign a letter grade than a numeric value, however to get the final grade I
need a numeric value.

Based on the following is it possible to create a formula for Excel 2003
that will assign a number to a cell when a letter is entered in another cell.

eg. if a HD+ = 5 points, HD = 4.5 points, HD = 4 points etc and I enter HD+
in say cell A2 and want the numeric grade to appear in cell F2 what formula
would I need to enter in cell F2 to get the numeric grade so that it could be
added with other numeric grades in the criterion to award a final grade for
the criterion. Some criteria have 3 sub criteria and a student might get a
HD, C and P- and the final grade would be the result of the three numeric
values added together and then included with the other criteria in other
sections.

Also is there a formula that can be used to only assign a final grade to a
cell in the event that all other relevent cells have a grade assigned in
them? for example if a student fails to submit and assessment item I do not
want to assign a final grade. Can Excel 2003 stop the final grade cell being
completed based on a formula in the cell?

Thank you very much for your help with this.