Results 1 to 15 of 51
Thread: Help Me Figure Out a Formula?

20140819, 07:00 #1
 Join Date
 Jun 2001
 Location
 USA
 Posts
 675
 Thanks
 36
 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.

20140819, 07:59 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Perhaps this example will work.
Steve

The Following User Says Thank You to sdckapr For This Useful Post:
Jon5 (20140830)

20140819, 21:47 #3
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,694
 Thanks
 72
 Thanked 358 Times in 327 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

The Following User Says Thank You to Maudibe For This Useful Post:
Jon5 (20140830)

20140820, 05:42 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 7,091
 Thanks
 243
 Thanked 978 Times in 894 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
Last edited by RetiredGeek; 20140820 at 05:45.

The Following User Says Thank You to RetiredGeek For This Useful Post:
Jon5 (20140830)

20140820, 05:52 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,694
 Thanks
 72
 Thanked 358 Times in 327 Posts
RG,
Always thinking!

20140820, 05:55 #6
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 7,091
 Thanks
 243
 Thanked 978 Times in 894 Posts
Maud,
You know us Retired Guys not much else to do! ROTFLOL.gif

20140820, 07:34 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 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

The Following 2 Users Say Thank You to sdckapr For This Useful Post:
Jon5 (20140830),RetiredGeek (20140820)

20140820, 07:54 #8
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 7,091
 Thanks
 243
 Thanked 978 Times in 894 Posts
Steve,

20140830, 17:48 #9
 Join Date
 Jun 2001
 Location
 USA
 Posts
 675
 Thanks
 36
 Thanked 1 Time in 1 Post
Hi Steve  Many thanks for your reply and the attachment. This did the trick!
Untitled.png

20140830, 18:01 #10
 Join Date
 Jun 2001
 Location
 USA
 Posts
 675
 Thanks
 36
 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!

20140830, 18:02 #11
 Join Date
 Jun 2001
 Location
 USA
 Posts
 675
 Thanks
 36
 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!

20140830, 18:03 #12
 Join Date
 Jun 2001
 Location
 USA
 Posts
 675
 Thanks
 36
 Thanked 1 Time in 1 Post
Thanks, Steve! You guys are the absolute best!

20140830, 18:23 #13
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 7,091
 Thanks
 243
 Thanked 978 Times in 894 Posts

20140929, 00:48 #14
 Join Date
 Jun 2001
 Location
 USA
 Posts
 675
 Thanks
 36
 Thanked 1 Time in 1 Post
Hi guys 
Sorry to reopen this thread; I have a quick followup.
Any idea how I can incorporate averages in row 22? I'm attaching the latest copy of the spreadsheet.
Thanks a million!

20140929, 03:47 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 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

The Following User Says Thank You to sdckapr For This Useful Post:
Jon5 (20140929)