Create Custom Function to Assign Number to Letter

Shelby21

New Member
Joined
Nov 21, 2017
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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.

gpa.PNG
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,935
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Try using this formula in cell L3 (and copy it down)...
Excel Formula:
=IFERROR(K3*(69-CODE(J3)),"")
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,828
Office Version
  1. 365
Platform
  1. Windows
Another option for L3
Excel Formula:
=IF(J3="","",FIND(J3,".CBA")*K3)
 

Shelby21

New Member
Joined
Nov 21, 2017
Messages
41
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Sep 21, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
or with a vba function:
VBA Code:
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

New Member
Joined
Nov 21, 2017
Messages
41
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

or with a vba function:
VBA Code:
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:

grade.PNG
 

rmwitt

New Member
Joined
Sep 21, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
hum...
i copy and pasted the same code into a new workbook, inserted a new module 1, entered the data....worked for me.

1635261994259.png


no idea what the difference may be...
 

rmwitt

New Member
Joined
Sep 21, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
grade is a string
hours are integers
1635262317718.png


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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,828
Office Version
  1. 365
Platform
  1. Windows
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? :confused:
 

Forum statistics

Threads
1,148,140
Messages
5,745,028
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top