| 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 up-to-date letter grade on that sheet. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Mar 22, 2006 11:45 am Post subject: |
|
|
It's very simple if you rearrange your lookup-table:
| 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 up-to-date 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
|
