	AllExperts > Excel
	Search

About Miguel Zapico
Expertise
I will try to answer any question that I can replicate in my current environment (I use Excel 2003 with Windows XP). This may include formula related questions on other Excel versions. Due to time limitations, I am not writing custom code to answer questions any more. Sorry for the inconvenience.

Experience
I have worked with Excel for the past 12 years, in various environments.

Organizations
NYPC (New York PC users group)

Organizations
NYPC (New York PC users group)

Education/Credentials
MCSE in Windows NT

You are here: Experts > Computing/Technology > Microsoft Software > Excel > User Defined Function

Excel - User Defined Function

Expert: Miguel Zapico - 6/16/2008

Question
I have used the following UDF and it worked well.(see below) But now I want to do the opposite and have the numeric value equal the Grade eg 15 = A+, not sure what it should say.

Can you help, I have no idea of the language, just a mum helping my daughter design a grading system for her students.

Function Grades(Letter As String) As Integer
Select Case Letter
  Case Is = "A+"
      Grades = 15
  Case Is = "A"
      Grades = 14
  Case Is = "A-"
      Grades = 13
  Case Is = "B+"
      Grades = 12
  Case Is = "B"
      Grades = 11
  Case Is = "B-"
      Grades = 10
  Case Is = "C+"
      Grades = 9
  Case Is = "C"
      Grades = 8
  Case Is = "C-"
      Grades = 7
  Case Is = "D+"
      Grades = 6
  Case Is = "D"
      Grades = 5
  Case Is = "D-"
      Grades = 4
  Case Is = "F+"
      Grades = 3
  Case Is = "F"
      Grades = 2
  Case Is = "F-"
      Grades = 1
End Select
End Function

Many thanks Jenny from Oz

Answer
You can reverse the function with something like:

Function NumGrades(Grade As Integer) As String
Select Case Grade
Case Is = 15
     NumGrades = "A+"
Case Is = 14
     NumGrades = "A"
Case Is = 13
     NumGrades = "A-"
Case Is = 12
     NumGrades = "B+"
Case Is = 11
     NumGrades = "B"
Case Is = 10
     NumGrades = "B-"
Case Is = 9
     NumGrades = "C+"
Case Is = 8
     NumGrades = "C"
Case Is = 7
     NumGrades = "C-"
Case Is = 6
     NumGrades = "D+"
Case Is = 5
     NumGrades = "D"
Case Is = 4
     NumGrades = "D-"
Case Is = 3
     NumGrades = "E+"
Case Is = 2
     NumGrades = "E"
Case Is = 1
     NumGrades = "E-"
End Select
End Function

I have changed the name of the function so the two of them can exist together in the same workbook.

Hope this helps,
Miguel

Add to this Answer Ask a Question

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here