MS Excel: Create custom function to exceed 7 nested If functions in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, I have a formula that I am using to test for 7 conditions, and each condition if true will return a different value. However, I now need to test a total of 12 possible values. The limitation of the nested IFs is that you can only nest up to 7. Is there an alternative to this formula to test so that I can test for 12 values instead of 7?

=IF(A1="10X12",140,IF(A1="8x8",64,IF(A1="6x6",36,IF(A1="8x10",80,IF(A1="14x16",224,IF(A1="9x9",81,IF(A1="4x3",12)))))))

Answer: There is no built-in alternative formula in Excel, but you could write your own function in VBA and then call this new function instead.

Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In our spreadsheet, we've created a custom VBA function called CalcValue. This function accepts as a parameter a cell and returns a value based on a complex IF THEN ELSE statement. You can use this method to nest up to or more than 7 IF conditions.

You can press Alt-F11 to view the VBA code.

Macro Code:

The macro code looks like this:

Function CalcValue(pVal As String) As Long

    If pVal = "10x12" Then
        CalcValue = 140

    ElseIf pVal = "8x8" Then
        CalcValue = 64

    ElseIf pVal = "6x6" Then
        CalcValue = 36

    ElseIf pVal = "8x10" Then
        CalcValue = 80

    ElseIf pVal = "14x16" Then
        CalcValue = 224

    ElseIf pVal = "9x9" Then
        CalcValue = 81

    ElseIf pVal = "4x3" Then
        CalcValue = 12

    Else
        CalcValue = 0
    End If

End Function