# Thread: Help Me Figure Out a Formula?

1. ## 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. Perhaps this example will work.

Steve

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

Jon5 (2014-08-30)

4. 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

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```

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

Jon5 (2014-08-30)

6. 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"
End Select
End Function```
Note the Trim() function as I found out an accidental space like "D " kills the formula!

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

Jon5 (2014-08-30)

8. RG,
Always thinking!

9. Maud,

You know us Retired Guys not much else to do! ROTFLOL.gif

10. 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 vPct As Variant

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
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. Steve,

13. Hi Steve - Many thanks for your reply and the attachment. This did the trick!

Untitled.png

14. 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. 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. Thanks, Steve! You guys are the absolute best!

17. Originally Posted by Jon5
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.

18. 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!

19. 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"
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 4 123 ... Last

#### Posting Permissions

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