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,.2 5,.1,.1})
where A1:I1 i the range where the grades are stored and the second part
arethe weights totalling one


"Angelo D" wrote in message
...
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,.2 5,.1,.1})
where A1:I1 i the range where the grades are stored and the second part
arethe weights totalling one


"Angelo D" wrote in message
...
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