View previous topic :: View next topic 
Author 
Message 
rudagar General User
Joined: 21 Jun 2005 Posts: 21

Posted: Wed Mar 22, 2006 8:16 am Post subject: Find Letter Grade 


I am trying to figure out a way to do this, does anyone know if it can be done?
Code:  A+ 90 100
A 85 89.99
A 80 84.99
B+ 74 79.99
B 68 73.99
B 62 67.99
C+ 56 61.99
C 50 55.99
D 40 49.99
F 0 39.99 
Lets say that is A1:C10... and for easy working sake, the mark is in D1. I want to be able to return the letter grade where D1 lies between Bx and Cx... something like SUMPRODUCT((D1 >= B1:B10)*(D1 <= C1:C10)*(A1:A10)), but that doesn't work.
For example, you have a 72, so it would show your letter grade as being B... I would work out to be {0,0,0,0,1,1,1,1,1,1)*(1,1,1,1,1,0,0,0,0,0)*("A+","A","A","B+","B","B","C+","C","D","F") = "B". 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Wed Mar 22, 2006 10:30 am Post subject: Re: Find Letter Grade 


It's difficult to not assume this might be a homework assignment, but pessimism aside, one possibility ...
Mark is in D1. In E1 put this:
IF(D1<89.99;"A+";IF(D1>84.99;"A";IF(D1>79.99;"A";IF(D1>73.99;"B+";IF(D1>67.99;"B";IF(D1>61.99;"B";IF(D1>55.99;"C+;IF(D1>49.99;"C";IF(D1>39.99;"D";"F")))))))))
Count the brackets [parentheses] to see that they match. This editor sucks, so it's difficult to do that. There may be 9 each way. To save possible typing errors copy/paste from here.
David.
Last edited by David on Wed Mar 22, 2006 10:34 am; edited 1 time in total 

Back to top 


rudagar General User
Joined: 21 Jun 2005 Posts: 21

Posted: Wed Mar 22, 2006 10:32 am Post subject: 


It's not a homework assignment. I have spreadsheets set up for all my courses to track my marks, and one course has a set letter grade scale... I'm just looking for an easy way to see my uptodate letter grade on that sheet. 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Wed Mar 22, 2006 11:45 am Post subject: 


It's very simple if you rearrange your lookuptable:
Code: 
0 F
40 D
50 C
56 C+
62 b
68 b
74 B+
80 a
85 a
90 A+

Value (0 to 100) in D1
Formula E1: =VLOOKUP(D1;$B$1:$C$10;2;1)
Notice the 4th param of VLOOKUP. If it's TRUE (anything but 0) then you get the next smallest match from a sorted 1st column rather than #NA for no match. 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Wed Mar 22, 2006 2:00 pm Post subject: 


rudagar wrote:  It's not a homework assignment. I have spreadsheets set up for all my courses to track my marks, and one course has a set letter grade scale... I'm just looking for an easy way to see my uptodate letter grade on that sheet. 
If you do the copy/paste, that should help. I also thought of Villeroy's approach, but thought you might be more familiar with IF() than the Lookup(), which sometimes gives people trouble for one reason or another, they being left wondering why numbers within the range of values show do in fact work for example. Villeroy explained that nicely for you.
David. 

Back to top 


rudagar General User
Joined: 21 Jun 2005 Posts: 21

Posted: Wed Mar 22, 2006 6:15 pm Post subject: 


I copied both of the formulas directly... for the VLOOKUP, I'm getting N/A, for the nested IFs I'm getting Err:509 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Wed Mar 22, 2006 7:43 pm Post subject: 


rudagar wrote:  I copied both of the formulas directly... for the VLOOKUP, I'm getting N/A, for the nested IFs I'm getting Err:509 
I wasn't too careful [but neither were you?]
Try this in the cell of your choice with a mark in D1:
=IF(D1<89.99;"A+";IF(D1>84.99;"A";IF(D1>79.99;"A";IF(D1>73.99;"B+";IF(D1>67.99;"B";IF(D1>61.99;"B";IF(D1>55.99;"C+";IF(D1>49.99;"C";IF(D1>39.99;"D";"F")))))))))
For the Lookup I see nothing wrong with the formula. Make sure the two columns are listed as shown in columns B and C. The reference need not be absolute for this purpose. There may be a slight correction in the column data though, if Villeroy doesn't mind:
0 F
40 D
50 C
56 C+
62 B
68 B
74 B+
80 A
85 A
90 A+
David. 

Back to top 




You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group
