Converting a letter grade to a numerical value

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.

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


"confused teacher" wrote:

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

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" wrote:

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

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

Brian

"Greg Wilson" wrote:

> 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" wrote:
>
> > 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.
> >
> >
> >