+ Reply to Thread
Results 1 to 13 of 13

Assign Consistent Letter Value to Grade percentage

  1. #1
    Registered User
    Join Date
    10-09-2008
    Location
    East TN
    Posts
    16

    Assign Consistent Letter Value to Grade percentage

    Newbie here. I have a very frustrating problem. I am using excel for my gradebook at school. I've tried several different ways to assign a letter grade to an number average. It works fine each different way I do it, VLOOKUP, IF, etc. But, I have about 5 grades out of 100 which give the wrong letter grade. For instance, my scale says that a grade of 85 should be lowest limit of a "B", but I get a "C" returned in the cell instead. Like I said, it only happens on a few grades. The biggest majority work fine. I can't figure out why. Any ideas? Attached is one of the "problems" with a student's grades. Note the Final Avg with a grade of "C", it should be "B".
    Attached Files Attached Files
    Last edited by Keel McDonald; 10-09-2008 at 02:52 PM.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Keel McDonald,

    Welcome to the forum. Please take a few minutes to read the forum rules below and then amend your title

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    10-09-2008
    Location
    East TN
    Posts
    16
    Thanks for helping.
    Last edited by Keel McDonald; 10-09-2008 at 01:20 PM.

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Your formula returns C as D3 = 83 which is less than 85 (the condition for c)

    =IF(D3<70,"F",IF(D3<75,"D",IF(D3<85,"C",IF(D3<93,"B",IF(D3>=93,"A","")))))
    Guess you need to list all the Grades you require. Currently you only have

    <70...........F
    <75...........D
    <85...........C
    <93...........B
    >93...........A

    What happens if 93 exactly??

    VBA Noob

  5. #5
    Registered User
    Join Date
    10-09-2008
    Location
    East TN
    Posts
    16
    Quote Originally Posted by VBA Noob View Post
    Your formula returns C as D3 = 83 which is less than 85 (the condition for c)



    Guess you need to list all the Grades you require. Currently you only have

    <70...........F
    <75...........D
    <85...........C
    <93...........B
    >93...........A

    What happens if 93 exactly??

    VBA Noob
    In the formula I have D3>=93, meaning greater than or equal to 93. I'm not sure what you mean by D3=83, in the spreadsheet it has a value of 85.
    Last edited by Keel McDonald; 10-09-2008 at 01:33 PM.

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    So u have

    Still don't have B+

    =IF(D3<70,"F",IF(D3<75,"D",IF(D3<85,"C",IF(D3<93,"B",IF(D3>=93,"A","")))))
    VBA Noob

  7. #7
    Registered User
    Join Date
    10-09-2008
    Location
    East TN
    Posts
    16
    I don't need to use "B+", or "B-", only "B".

  8. #8
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    grade of 85 should be lowest limit of a "B", but I get a "C"
    If you format cell D3 as general you will see it returns 84.86 which is less than 85

    Try using the round function

    IF(ROUND(D3,0)<85
    or try

    =LOOKUP(ROUND(D4,0),{0,70,75,85,93,94},{"F","D","C","B","A"})
    VBA Noob

  9. #9
    Registered User
    Join Date
    10-09-2008
    Location
    East TN
    Posts
    16
    Quote Originally Posted by VBA Noob View Post
    If you format cell D3 as general you will see it returns 84.86 which is less than 85

    Try using the round function



    or try



    VBA Noob
    That worked great, but now I have a new problem. I've attached a different student and now the Grade shows as "#N/A". This only happened on 2 students, but not the same students as the above problem. Any idea why?

  10. #10
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Not without seeing an example

    VBA Noob

  11. #11
    Registered User
    Join Date
    10-09-2008
    Location
    East TN
    Posts
    16
    Sorry, forgot to attach example. Here you go.
    Attached Files Attached Files

  12. #12
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Had a extra condition.

    Should have being

    =LOOKUP(ROUND(D4,0),{0,70,75,85,94},{"F","D","C","B","A"})
    VBA Noob

  13. #13
    Registered User
    Join Date
    10-09-2008
    Location
    East TN
    Posts
    16
    YOU'RE THE MAN!! That fixed it. I've been working on this for 2 days. Thanks!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Averaging grades
    By RandomName in forum Excel General
    Replies: 6
    Last Post: 09-03-2008, 03:27 PM
  2. Counting Higher and Foundation grades.
    By edmdas in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-25-2008, 05:28 AM
  3. Figuring letter grades
    By mkingsley in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-31-2008, 10:10 AM
  4. Student Grades Distribution Chart
    By sjbuck in forum Excel General
    Replies: 7
    Last Post: 11-27-2007, 12:44 PM
  5. Replies: 2
    Last Post: 06-21-2007, 04:01 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1