# 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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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

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
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

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:

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

##### 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
199
Replies
7
Views
203
Replies
2
Views
746
Replies
15
Views
248
Replies
3
Views
573

Threads
1,147,499
Messages
5,741,506
Members
423,663
Latest member
kaveh87rsh

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

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