# Help Me Figure Out a Formula?

Show 100 post(s) from this thread on one page
Page 1 of 4 123 ... Last
• 2014-08-19, 07:00
Jon5
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:

Attachment 39382

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:

Attachment 39383

Thanks a ton! (I'm terrible at math. :)
• 2014-08-19, 07:59
sdckapr
Perhaps this example will work.

Steve
• 2014-08-19, 21:47
Maudibe
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

Attachment 39392

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```
• 2014-08-20, 05:42
RetiredGeek
Building on Maud's fine work here's a version for the typing challenged, like me! :lol:
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!
:cheers:
• 2014-08-20, 05:52
Maudibe
RG,
Always thinking! :)
• 2014-08-20, 05:55
RetiredGeek
Maud,

You know us Retired Guys not much else to do! Attachment 39395
:cheers:
• 2014-08-20, 07:34
sdckapr
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
• 2014-08-20, 07:54
RetiredGeek
Steve,

:thewave:
• 2014-08-30, 17:48
Jon5
Hi Steve - Many thanks for your reply and the attachment. This did the trick!

Attachment 39483
• 2014-08-30, 18:01
Jon5
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!
• 2014-08-30, 18:02
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!
• 2014-08-30, 18:03
Jon5
Thanks, Steve! You guys are the absolute best!
• 2014-08-30, 18:23
RetiredGeek
Quote:

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. :cheers:
• 2014-09-29, 00:48
Jon5
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!
• 2014-09-29, 03:47
sdckapr
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
Show 100 post(s) from this thread on one page
Page 1 of 4 123 ... Last