# Create Custom Function to Assign Number to Letter

#### Shelby21

##### New Member
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.

### Excel Facts

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
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
Try using this formula in cell L3 (and copy it down)...
Excel Formula:
``=IFERROR(K3*(69-CODE(J3)),"")``

#### Peter_SSs

##### MrExcel MVP, Moderator
Another option for L3
Excel Formula:
``=IF(J3="","",FIND(J3,".CBA")*K3)``

#### Shelby21

##### New Member

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
or with a vba function:
VBA Code:
``````Function GradePts(ByRef sGrade As String, ByRef ihours As Integer) As Integer
Dim iResult As Integer

Case Is = "A"
iResult = 4
Case Is = "B"
iResult = 3
Case Is = "C"
iResult = 2
End Select

End Function``````

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

#### Shelby21

##### New Member

or with a vba function:
VBA Code:
``````Function GradePts(ByRef sGrade As String, ByRef ihours As Integer) As Integer
Dim iResult As Integer

Case Is = "A"
iResult = 4
Case Is = "B"
iResult = 3
Case Is = "C"
iResult = 2
End Select

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

##### New Member
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

##### New Member
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

##### MrExcel MVP, Moderator
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?

Replies
2
Views
200
Replies
7
Views
208
Replies
2
Views
758
Replies
15
Views
263
Replies
3
Views
585

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.

### Which adblocker are you using?

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

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