Help with grade calc formula, please

[rcol...@my-dejanews.com]

Hi, all. Will someone help me build a grade calculation formula for Excel97?

Previously I successfully used the following formula to convert percentages
into Alpha values of "A", "B", "C", "D", or "F":

=IF(AD8>=0.9,"A",IF(AD8>=0.8,"B",IF(AD8>=0.7,"C",IF(AD8>=0.6,"D",IF
(AD8>0,"F","NA")))))

Now, I'm having problems because I need to break grades down to "A+", "A",
"A-", "B+", "B", "B-", etc. whereby:

97 - 100 = A+
93 - 96 = A
90 - 92 = A-
87 - 89 = B+
83 - 86 = B
89 - 82 = B-
77 - 79 = C+
73 - 76 = C
70 - 72 = C-
67 - 69 = D+
63 - 66 = D
60 - 62 = D-
0 - 59 = F

The problem I run into is that I can't seem to nest more than seven IF
statements. Can someone who is much sharper than me offer some suggestions?

Thank you so much,

Bob Cole
rc...@oswego.edu

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

[Morten Hvistendahl]

This is one way to solve your problem. Enter the following User Defined VBA function into your Visual Basic Editor (Tools -->Macro -->Visual Basic Editor -->Insert -->Module). This function will be available under "User Defined" functions.

Function ConvAlpha(GradePoint)
Step1 = 59  'F
Step2 = 62  'D-
Step3 = 66  'D
Step4 = 69  'D+
Step5 = 72  'C-
Step6 = 76  'C
Step7 = 79  'C+
Step8 = 82  'B-
Step9 = 86  'B
Step10 = 89 'B+
Step11 = 92 'A-
Step12 = 96 'A

If GradePoint <= Step1 Then
    ConvAlpha = "F"
    ElseIf GradePoint <= Step2 Then
    ConvAlpha = "D-"
    ElseIf GradePoint <= Step3 Then
    ConvAlpha = "D"
    ElseIf GradePoint <= Step4 Then
    ConvAlpha = "D+"
    ElseIf GradePoint <= Step5 Then
    ConvAlpha = "C-"
    ElseIf GradePoint <= Step6 Then
    ConvAlpha = "C"
    ElseIf GradePoint <= Step7 Then
    ConvAlpha = "C+"
    ElseIf GradePoint <= Step8 Then
    ConvAlpha = "B-"
    ElseIf GradePoint <= Step9 Then
    ConvAlpha = "B"
    ElseIf GradePoint <= Step10 Then
    ConvAlpha = "B+"
    ElseIf GradePoint <= Step11 Then
    ConvAlpha = "A-"
    ElseIf GradePoint <= Step12 Then
    ConvAlpha = "A"
    Else
    ConvAlpha = "A+"
End If
End Function
 

[R.V. Mendoza]

Click on Tools->Macro->Visual Basic Editor.
In Visual Basic Editor, click on Insert->Module.
Copy and paste the following function into the module:

Public Function grade(score) As String
Select Case score
Case 97 To 100
grade = "A+"
Case 93 To 96
grade = "A"
Case 90 To 92
grade = "A-"
Case 87 To 89
grade = "B+"
Case 83 To 86
grade = "B"
Case 80 To 82
grade = "B-"
Case 77 To 79
grade = "C+"
Case 73 To 76
grade = "C"
Case 70 To 72
grade = "C-"
Case 67 To 69
grade = "D+"
Case 63 To 66
grade = "D"
Case 60 To 62
grade = "D-"
Case 0 To 59
grade = "F"
Case Else
grade = "Invalid score."
End Select
End Function

Close Visual Basic Editor. Examples on using the function
=grade(87)
=grade(A34)

rcol...@my-dejanews.com wrote in message
<6trca9$6oa$1...@nnrp1.dejanews.com>...