Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Angelo D
 
Posts: n/a
Default 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
  #2   Report Post  
N Harkawat
 
Posts: n/a
Default

=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



  #3   Report Post  
Jason Morin
 
Posts: n/a
Default

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

  #4   Report Post  
LanceB
 
Posts: n/a
Default

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

  #5   Report Post  
Angelo D
 
Posts: n/a
Default

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






  #6   Report Post  
Angelo D
 
Posts: n/a
Default

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

  #7   Report Post  
Angelo D
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Have A Letter Reference A Name For An Intoduction travelersway New Users to Excel 2 February 16th 05 06:47 PM
How do I set up having a letter = a value? Peo Sjoblom Excel Discussion (Misc queries) 0 January 31st 05 07:17 PM
roundoff when converting text to numbers Jack Excel Worksheet Functions 3 January 30th 05 02:51 AM
Replace Letter "E" with Letter "C" for 200 Different Sums in a Col Katherine Excel Worksheet Functions 1 January 21st 05 03:53 AM
How do I view the actual numeric value of a formula in Excel 2002. Excel Function Help Excel Worksheet Functions 0 January 13th 05 11:07 PM


All times are GMT +1. The time now is 10:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"