Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Mar 2016
    Posts
    14
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Conversion of Raw Scores to Numerical Grade Equivalent

    Hi guys,

    I need your help! I want to create an excel file that will be used to convert raw scores into numerical grades based on the following parameters:

    1. If score is perfect (i.e. 100) then the grade should be 95)
    2. Passing grade should be 80
    3. Lowest score is 0 which is equivalent to a grade of 0.

    I have attached an excel file for reference with certain comments.

    I hope to get a response from you all.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I'm not clear what you want. As it stands, it appears that you simply want to return the score capped at 95?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Mar 2016
    Posts
    14
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Rory,

    Basically what I wanted is to get an equivalent grade for a score wherein the highest grade is 95. i.e, if the test is a total of 100 and a participant gets the perfect 100, 95 should be the grade. likewise, if a participant, gets a score of 93, then an equivalent grade should be computed by excel.

    For the passing score, what i wanted to have is a formula that would compute the passing score that would be indicated in B3 based on the highest score (B2) and the passing grade (as indicated in e3).

    I hope you will be able to understand the concern.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The first part sounds like you simply want to multiply the score by 95%

    The second you want to divide the passing grade by 95%
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,411
    Thanks
    447
    Thanked 406 Times in 378 Posts
    Here's a simple formula you could use.

    Assuming the original grade is in cell A1, and the adjusted grade is in cell B1:

    Put the following formula into cell B1: =(A1*0.95)
    Adjust the formatting of cell B1 so that it doesn't show any decimal places.

    Now type the original grade into cell A1.
    The adjusted grade will then appear in cell B1.

    If you then copy what is in cell B1, and then paste it into a bunch of cells immediately below B1, Excel will put the correct formula into each one of those cells.
    You can then type the original grades into A1, A2, A3, etc., and the adjusted grade will appear to the right in the B column.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi tadzkie

    ..see attached file.
    This correlates one scoring range to another.
    (The clue was in the file name you used - transmutation)
    I've put formulas in for you.

    I've added some conditional formatting to the Score range.
    Even if you didn't ask for it.
    Because I liked the colour blending from yellow to green as you go from the Pass level to the top score.

    zeddy
    Attached Files Attached Files

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    That's our Zeddy Mutant Spreadsheet Turtle!
    ZeddyMutant.PNG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    New Lounger
    Join Date
    Mar 2016
    Posts
    14
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi everyone!

    Thanks for the big help! I used zeddy's formula...



    Consider this solved.

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    ..so you didn't like my formatting then????

  10. #10
    New Lounger
    Join Date
    Mar 2016
    Posts
    14
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi zeddy,

    Actually, i used the excel file that you have attached.

    Now, I was given another condition for this conversion/transmutation. For the "equivalent grade", lowest grade should be 65 instead of 0 and the highest grade still remains at 95.

    Can anyone help me with it? Maybe tweak what zeddy made.

    Thank yoy!

Posting Permissions

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