Create Custom Function to Assign Number to Letter

[Shelby21]

Hi guys,

I'm trying to create a quick GPA calculator.

What I would like to do is create a custom function that assigns the following without changing the letter grade value I input into column J:

A = 4
B = 3
C = 2

The result should be when I input the formula into L3 to calculate grade points should be J3 * K3 and the result should be 16

I really don't want to use any lookup table function as I think creating a custom function would be much simpler and cleaner, but I am open to suggestions.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Rick Rothstein]

Try using this formula in cell L3 (and copy it down)...

=IFERROR(K3*(69-CODE(J3)),"")

 

[Peter_SSs]

Another option for L3

=IF(J3="","",FIND(J3,".CBA")*K3)

 

[Shelby21]

Hi Fluff,
Thank you for your suggestion. I updated my account details to show what version of Excel and OS platform I am using for future inquiries.

Hi Rick,
Thank you for your response. I used your formula and it worked perfectly. I wasn't familiar with CODE function until I googled it and now I understand what the function is doing.
This is much simpler as I was thinking this had to be done with VBA. Thank you for your help!

 

[rmwitt]

or with a vba function:

Function GradePts(ByRef sGrade As String, ByRef ihours As Integer) As Integer
    Dim iResult As Integer
    
    Select Case StrConv(sGrade, vbProperCase)
        Case Is = "A"
            iResult = 4 
        Case Is = "B"
            iResult = 3 
        Case Is = "C"
            iResult = 2 
    End Select
    
    GradePts = iResult *ihours
        
End Function

enter the function in your workbook as '=GradePts( j3 , k3 )' in M3 and copy down.

 

[Shelby21]

or with a vba function:

Function GradePts(ByRef sGrade As String, ByRef ihours As Integer) As Integer
    Dim iResult As Integer
   
    Select Case StrConv(sGrade, vbProperCase)
        Case Is = "A"
            iResult = 4
        Case Is = "B"
            iResult = 3
        Case Is = "C"
            iResult = 2
    End Select
   
    GradePts = iResult *ihours
       
End Function

enter the function in your workbook as '=GradePts( j3 , k3 )' in M3 and copy down.

Hi rmwitt,

Thank you for your vba code suggestion. I was definitely looking for a way to do this using vba.

The code you provided did not work correctly though.

After copy/pasting the code into developer and inputting the function into Excel I got the following output below for L13:

 

[rmwitt]

hum...
i copy and pasted the same code into a new workbook, inserted a new module 1, entered the data....worked for me.

no idea what the difference may be...

 

[rmwitt]

grade is a string
hours are integers

your syntax looks correct. #Name indicates that the function does not exist. did you insert the code into a new module in the same workbook as the data? Try starting a new workbook from scratch...

 

[Peter_SSs]

I'm wondering why a user-defined function is even being considered when very simple formulas using standard worksheet functions have already been shown to do the job?