[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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".
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 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
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.
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 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.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

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.
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
David
Super User

Joined: 24 Oct 2003
Posts: 5668

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.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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