Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have A Letter Reference A Name For An Intoduction | New Users to Excel | |||
How do I set up having a letter = a value? | Excel Discussion (Misc queries) | |||
roundoff when converting text to numbers | Excel Worksheet Functions | |||
Replace Letter "E" with Letter "C" for 200 Different Sums in a Col | Excel Worksheet Functions | |||
How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions |