Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jun 2001
    Location
    USA
    Posts
    648
    Thanks
    25
    Thanked 1 Time in 1 Post

    Help Me Figure Out a Formula?

    Hi there -

    I need some help figuring out the formula for a grading spreadsheet. Here's a screen shot:

    Untitled.png

    Anyone know what "=" calculation I use for the "final grade" column?

    Do I need to convert each letter to a number first? If so, here's the rubric:

    Untitled 2.png

    Thanks a ton! (I'm terrible at math.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Perhaps this example will work.

    Steve
    Attached Files Attached Files

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    Jon5 (2014-08-30)

  4. #3
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 227 Times in 210 Posts
    Jon,

    An alternative approach is a user defined function. Place the following code in a standard module then, using your image example, place the formula =Grade(B3:G3) in Cell H3 and copy down. The function will return the letter grade. No need for additional tables or complex fomulas

    HTH,
    Maud

    Grades.png

    Code:
    Public Function Grade(rng As Range) As String
    '------------------------------
    'DECLARE AND SET VARIABLES
    Dim score As Range
    Dim Total As Double
    Total = 0
    '------------------------------
    'CALCULATE TOTAL NUMERIC SCORE FROM GRADES
    For Each score In rng
        Select Case score
            Case "A+": Total = Total + (0.97 * Cells(2, score.Column))
            Case "A":  Total = Total + (0.93 * Cells(2, score.Column))
            Case "A-": Total = Total + (0.9 * Cells(2, score.Column))
            Case "B+": Total = Total + (0.87 * Cells(2, score.Column))
            Case "B":  Total = Total + (0.83 * Cells(2, score.Column))
            Case "B-": Total = Total + (0.8 * Cells(2, score.Column))
            Case "C+": Total = Total + (0.77 * Cells(2, score.Column))
            Case "C":  Total = Total + (0.73 * Cells(2, score.Column))
            Case "C-": Total = Total + (0.7 * Cells(2, score.Column))
            Case "D+": Total = Total + (0.67 * Cells(2, score.Column))
            Case "D":  Total = Total + (0.63 * Cells(2, score.Column))
            Case "D-": Total = Total + (0.6 * Cells(2, score.Column))
            Case "F":  Total = Total + 0
        End Select
    Next score
    '------------------------------
    'CONVERT FINAL NUMERIC SCORE TO FINAL LETTER GRADE
        Select Case Total
            Case Is >= 0.97: Grade = "A+"
            Case Is >= 0.93: Grade = "A"
            Case Is >= 0.9: Grade = "A-"
            Case Is >= 0.87: Grade = "B+"
            Case Is >= 0.83: Grade = "B"
            Case Is >= 0.8: Grade = "B-"
            Case Is >= 0.77: Grade = "C+"
            Case Is >= 0.73: Grade = "C"
            Case Is >= 0.7: Grade = "C-"
            Case Is >= 0.67: Grade = "D+"
            Case Is >= 0.63: Grade = "D"
            Case Is >= 0.6: Grade = "D-"
            Case Is >= 0: Grade = "F"
        End Select
    End Function
    Attached Files Attached Files

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    Jon5 (2014-08-30)

  6. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,192
    Thanks
    201
    Thanked 781 Times in 715 Posts
    Building on Maud's fine work here's a version for the typing challenged, like me!
    Code:
    'DECLARE AND SET VARIABLES
      Dim score As Range
      Dim Total As Double
      Dim dPct  As Double
      Total = 0
    '------------------------------
    'CALCULATE TOTAL NUMERIC SCORE FROM GRADES
    For Each score In rng
        Select Case Trim(score)   
            Case "A+": dPct = 0.97
            Case "A":  dPct = 0.93
            Case "A-": dPct = 0.9
            Case "B+": dPct = 0.87
            Case "B":  dPct = 0.83
            Case "B-": dPct = 0.8
            Case "C+": dPct = 0.77
            Case "C":  dPct = 0.73
            Case "C-": dPct = 0.7
            Case "D+": dPct = 0.67
            Case "D":  dPct = 0.63
            Case "D-": dPct = 0.6
            Case "F": dPct = 0
        End Select
           Total = Total + (dPct * Cells(2, score.Column))
    Next score
    '------------------------------
    'CONVERT FINAL NUMERIC SCORE TO FINAL LETTER GRADE
        Select Case Total
            Case Is < 0.6: Grade = "F"
            Case Is < 0.63: Grade = "D-"
            Case Is < 0.67: Grade = "D"
            Case Is < 0.7: Grade = "D+"
            Case Is < 0.73: Grade = "C-"
            Case Is < 0.77: Grade = "C"
            Case Is < 0.8: Grade = "C+"
            Case Is < 0.83: Grade = "B-"
            Case Is < 0.87: Grade = "B"
            Case Is < 0.9: Grade = "B+"
            Case Is < 0.93: Grade = "A-"
            Case Is < 0.97: Grade = "A"
            Case Else: Grade = "A+"
        End Select
    End Function
    Note the Trim() function as I found out an accidental space like "D " kills the formula!
    Last edited by RetiredGeek; 2014-08-20 at 05:45.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Jon5 (2014-08-30)

  8. #5
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 227 Times in 210 Posts
    RG,
    Always thinking!

  9. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,192
    Thanks
    201
    Thanked 781 Times in 715 Posts
    Maud,

    You know us Retired Guys not much else to do! ROTFLOL.gif
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  10. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    An alternative which only requires entering the values in 1 place is to use match/index.

    Code:
    Option Explicit
    Public Function Grade(rng As Range) As String
    'DECLARE AND SET VARIABLES
      Dim rScore As Range
      Dim dTotal As Double
      Dim dPct  As Double
      Dim vGrades As Variant
      Dim vPct As Variant
      
    'DEFINE GRADES RANGES
      vGrades = Array("F", "D-", "D", "D+", "C-", "C", "C+", "B-", "B", "B+", "A-", "A", "A+")
      vPct = Array(0, 0.6, 0.63, 0.67, 0.7, 0.73, 0.77, 0.8, 0.83, 0.87, 0.9, 0.93, 0.97)
      
      dTotal = 0
    'CALCULATE TOTAL NUMERIC SCORE FROM GRADES
      For Each rScore In rng
        dPct = vPct(Application.WorksheetFunction.Match(Trim(rScore), vGrades, 0) - 1)
        dTotal = dTotal + (dPct * Cells(2, rScore.Column))
      Next rScore
    'CONVERT FINAL NUMERIC SCORE TO FINAL LETTER GRADE
        Grade = vGrades(Application.WorksheetFunction.Match(dTotal, vPct) - 1)
    End Function
    Steve

  11. The Following 2 Users Say Thank You to sdckapr For This Useful Post:

    Jon5 (2014-08-30),RetiredGeek (2014-08-20)

  12. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,192
    Thanks
    201
    Thanked 781 Times in 715 Posts
    Steve,

    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  13. #9
    5 Star Lounger
    Join Date
    Jun 2001
    Location
    USA
    Posts
    648
    Thanks
    25
    Thanked 1 Time in 1 Post
    Hi Steve - Many thanks for your reply and the attachment. This did the trick!

    Untitled.png

  14. #10
    5 Star Lounger
    Join Date
    Jun 2001
    Location
    USA
    Posts
    648
    Thanks
    25
    Thanked 1 Time in 1 Post
    Hi Maud - It took me a minute to figure out how this worked (Alt+F11 , but this is awesome! So taut and clean! Thanks a million!

  15. #11
    5 Star Lounger
    Join Date
    Jun 2001
    Location
    USA
    Posts
    648
    Thanks
    25
    Thanked 1 Time in 1 Post
    Thanks, RG! This is perfect!

    For the even more challenged folk like me, just one thing: you need to add the line "Public Function Grade(rng As Range) As String" at the top, without the quotes.

    Thanks again!

  16. #12
    5 Star Lounger
    Join Date
    Jun 2001
    Location
    USA
    Posts
    648
    Thanks
    25
    Thanked 1 Time in 1 Post
    Thanks, Steve! You guys are the absolute best!

  17. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,192
    Thanks
    201
    Thanked 781 Times in 715 Posts
    Quote Originally Posted by Jon5 View Post
    Thanks, RG! This is perfect!

    For the even more challenged folk like me, just one thing: you need to add the line "Public Function Grade(rng As Range) As String" at the top, without the quotes.

    Thanks again!
    Joh,

    Sorry my fingers slipped when doing the highlight for the copy the line was there in my test code.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  18. #14
    5 Star Lounger
    Join Date
    Jun 2001
    Location
    USA
    Posts
    648
    Thanks
    25
    Thanked 1 Time in 1 Post
    Hi guys -

    Sorry to reopen this thread; I have a quick follow-up.

    Any idea how I can incorporate averages in row 22? I'm attaching the latest copy of the spreadsheet.

    Thanks a million!
    Attached Files Attached Files

  19. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Does this modification to your code work? I just took the total you get and divided it by the number of cells in the range and then convert that number.
    Code:
    Public Function AvgGrade(rng As Range) As String
    'DECLARE AND SET VARIABLES
      Dim score As Range
      Dim Total As Double
      Dim dPct  As Double
      Total = 0
    '------------------------------
    'CALCULATE TOTAL NUMERIC SCORE FROM GRADES
    For Each score In rng
        Select Case Trim(score)
            Case "A+": dPct = 0.97
            Case "A":  dPct = 0.93
            Case "A-": dPct = 0.9
            Case "B+": dPct = 0.87
            Case "B":  dPct = 0.83
            Case "B-": dPct = 0.8
            Case "C+": dPct = 0.77
            Case "C":  dPct = 0.73
            Case "C-": dPct = 0.7
            Case "D+": dPct = 0.67
            Case "D":  dPct = 0.63
            Case "D-": dPct = 0.6
            Case "F": dPct = 0
        End Select
           Total = Total + dPct
    Next score
           Total = Total / rng.Count
    '------------------------------
    'CONVERT FINAL NUMERIC SCORE TO FINAL LETTER GRADE
        Select Case Total
            Case Is < 0.6: AvgGrade = "F"
            Case Is < 0.63: AvgGrade = "D-"
            Case Is < 0.67: AvgGrade = "D"
            Case Is < 0.7: AvgGrade = "D+"
            Case Is < 0.73: AvgGrade = "C-"
            Case Is < 0.77: AvgGrade = "C"
            Case Is < 0.8: AvgGrade = "C+"
            Case Is < 0.83: AvgGrade = "B-"
            Case Is < 0.87: AvgGrade = "B"
            Case Is < 0.9: AvgGrade = "B+"
            Case Is < 0.93: AvgGrade = "A-"
            Case Is < 0.97: AvgGrade = "A"
            Case Else: AvgGrade = "A+"
        End Select
    End Function
    Steve

  20. The Following User Says Thank You to sdckapr For This Useful Post:

    Jon5 (2014-09-29)

Page 1 of 2 12 LastLast

Posting Permissions

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