OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Find Letter Grade

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
rudagar
General User
General User


Joined: 21 Jun 2005
Posts: 21

PostPosted: Wed Mar 22, 2006 8:16 am    Post subject: Find Letter Grade Reply with quote

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
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Wed Mar 22, 2006 10:30 am    Post subject: Re: Find Letter Grade Reply with quote

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
View user's profile Send private message
rudagar
General User
General User


Joined: 21 Jun 2005
Posts: 21

PostPosted: Wed Mar 22, 2006 10:32 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Mar 22, 2006 11:45 am    Post subject: Reply with quote

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
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Wed Mar 22, 2006 2:00 pm    Post subject: Reply with quote

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
View user's profile Send private message
rudagar
General User
General User


Joined: 21 Jun 2005
Posts: 21

PostPosted: Wed Mar 22, 2006 6:15 pm    Post subject: Reply with quote

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
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Wed Mar 22, 2006 7:43 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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