Converting Letter Grades to Numeric

I am working on a spreadsheet in Excel for Grades for my students. I need to
set letter grades to numeric equivalents (A = 4.0, A- = 3.7, etc) then have
the final grade show up as a Letter (A, A-, B+, etc.) Part II of this is I
have 9 grades each equal to a certain % of the Final grade (totaling
100%).... Help! Thanks Angelo D

=LOOKUP(A1,{"a","b"},{4,3.70})
where cell A1 holds the letter grades and this way keep extending the values
in the curly separating them by a comma.

For Part II you can use something like this
=sumproduct((A1:I1),{0.1,0.1,0.2,0.05,0.05,0.05,.25,.1,.1})
where A1:I1 i the range where the grades are stored and the second part
arethe weights totalling one


"Angelo D" <AngeloD@discussions.microsoft.com> wrote in message
news:2CF8468A-2CBA-4635-9693-C77A63BD95AA@microsoft.com...
>I am working on a spreadsheet in Excel for Grades for my students. I need
>to
> set letter grades to numeric equivalents (A = 4.0, A- = 3.7, etc) then
> have
> the final grade show up as a Letter (A, A-, B+, etc.) Part II of this is
> I
> have 9 grades each equal to a certain % of the Final grade (totaling
> 100%).... Help! Thanks Angelo D

This will get you started in the right direction:

=LOOKUP(A1,{0,"F";2,"C";2.3,"C+";2.7,"B-";3,"B";3.3,"B+";3.7,"A-";4,"A"})

HTH
Jason
Atlanta, GA

"Angelo D" wrote:

> I am working on a spreadsheet in Excel for Grades for my students. I need to
> set letter grades to numeric equivalents (A = 4.0, A- = 3.7, etc) then have
> the final grade show up as a Letter (A, A-, B+, etc.) Part II of this is I
> have 9 grades each equal to a certain % of the Final grade (totaling
> 100%).... Help! Thanks Angelo D

Col A Col B
B- 2.7
A 4
D+ 1.3
C 2.3
C+ 2.3
F 0
B 3
C 2.229 <- =AVERAGE(B1:B7)

In b1
=VLOOKUP(LEFT(A1,1),{"A",4;"B",3;"C",2;"D",1;"F",0},2,0)+IF(LEN(A1)=2,RIGHT(A1,1)&0.3,0)

in a8 across from the average in b8
=VLOOKUP(B8,{0,"F";0.7,"D-";1,"D";1.3,"D+";1.7,"C-";2,"C";2.3,"C+";2.7,"B-";3,"B";3.3,"B+";3.7,"A-";4,"A"},2)

Good luck

Lance

"Angelo D" wrote:

> I am working on a spreadsheet in Excel for Grades for my students. I need to
> set letter grades to numeric equivalents (A = 4.0, A- = 3.7, etc) then have
> the final grade show up as a Letter (A, A-, B+, etc.) Part II of this is I
> have 9 grades each equal to a certain % of the Final grade (totaling
> 100%).... Help! Thanks Angelo D

Thanks so much for this -- I will work on it and get back to you if I have
more questions...... This site is the best thing! Have a great day

"N Harkawat" wrote:

> =LOOKUP(A1,{"a","b"},{4,3.70})
> where cell A1 holds the letter grades and this way keep extending the values
> in the curly separating them by a comma.
>
> For Part II you can use something like this
> =sumproduct((A1:I1),{0.1,0.1,0.2,0.05,0.05,0.05,.25,.1,.1})
> where A1:I1 i the range where the grades are stored and the second part
> arethe weights totalling one
>
>
> "Angelo D" <AngeloD@discussions.microsoft.com> wrote in message
> news:2CF8468A-2CBA-4635-9693-C77A63BD95AA@microsoft.com...
> >I am working on a spreadsheet in Excel for Grades for my students. I need
> >to
> > set letter grades to numeric equivalents (A = 4.0, A- = 3.7, etc) then
> > have
> > the final grade show up as a Letter (A, A-, B+, etc.) Part II of this is
> > I
> > have 9 grades each equal to a certain % of the Final grade (totaling
> > 100%).... Help! Thanks Angelo D
>
>
>

Thanks Jason: I appreciate all the help I've gotten from you and all the
others in the past few weeks! This site is the greatest!
Have a great day

"Jason Morin" wrote:

> This will get you started in the right direction:
>
> =LOOKUP(A1,{0,"F";2,"C";2.3,"C+";2.7,"B-";3,"B";3.3,"B+";3.7,"A-";4,"A"})
>
> HTH
> Jason
> Atlanta, GA
>
> "Angelo D" wrote:
>
> > I am working on a spreadsheet in Excel for Grades for my students. I need to
> > set letter grades to numeric equivalents (A = 4.0, A- = 3.7, etc) then have
> > the final grade show up as a Letter (A, A-, B+, etc.) Part II of this is I
> > have 9 grades each equal to a certain % of the Final grade (totaling
> > 100%).... Help! Thanks Angelo D

Lance:

Thanks for this -- I'm sure it will help.. I will post again if I have any
more quesitons.... I am so appreciative of your help and all the help I've
gotten from others in the past few weeks!

Have a great day!

"LanceB" wrote:

> Col A Col B
> B- 2.7
> A 4
> D+ 1.3
> C 2.3
> C+ 2.3
> F 0
> B 3
> C 2.229 <- =AVERAGE(B1:B7)
>
> In b1
> =VLOOKUP(LEFT(A1,1),{"A",4;"B",3;"C",2;"D",1;"F",0},2,0)+IF(LEN(A1)=2,RIGHT(A1,1)&0.3,0)
>
> in a8 across from the average in b8
> =VLOOKUP(B8,{0,"F";0.7,"D-";1,"D";1.3,"D+";1.7,"C-";2,"C";2.3,"C+";2.7,"B-";3,"B";3.3,"B+";3.7,"A-";4,"A"},2)
>
> Good luck
>
> Lance
>
> "Angelo D" wrote:
>
> > I am working on a spreadsheet in Excel for Grades for my students. I need to
> > set letter grades to numeric equivalents (A = 4.0, A- = 3.7, etc) then have
> > the final grade show up as a Letter (A, A-, B+, etc.) Part II of this is I
> > have 9 grades each equal to a certain % of the Final grade (totaling
> > 100%).... Help! Thanks Angelo D