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(O784.5%,"HD",IF(O774.5%,"D",IF(O764.5%,"C", IF(O749.5%,"P",IF(O739.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.