Many Nested IFs - Alternative

Question

I am designing a pricing tool for a client where the decision process to select a carrier type my exceed 7 consecutive "IF" statements.

If example, I may have more than 7 conditions required to pick a freight carrier.  How can I do this in excel without violating nested if limitations?

What are my options?

---

Kody_Devl

Answer 1

You could have helper columns for each IF condition and then use AND, OR, and NOT statements appropriately to determine your result.

You could write a user-defined function that would do all the logic for you.

My $0.02.

---

HTH,

Eric

Tu ne cede malis sed contra audentior ito

Answer 2

You could have helper columns for each IF condition and then use AND, OR, and NOT statements appropriately to determine your result.

You could write a user-defined function that would do all the logic for you.

My $0.02.

---

HTH,

Eric

Tu ne cede malis sed contra audentior ito

Answer 3

Thank you,

I wish that I could do some kind of Case statement.

---

Kody_Devl

Answer 4

Post your logic and we'll get you a solution.

---

gsnu201202

Answer 5

In Excel 2007 and later, you may nest 64 ifs.

However I do not recommend using nested if too much. Just creat a pricing table then use VLOOKUP insted of IF. Match and Index will also help.

Answer 6

Maybe you should consider Sumproduct, Index/Match, or Vlookup.  Also, you should be able to get a PivtoTable to do multiple synthetic 'IFs'.

---

Ryan Shuell

Answer 7

Kody:

As Gary's Student (The moderator) pointed out, if you post your logic, it would be helpful.  I have had similar very complex selection criteria, and in most cases, if you are able to arrange your logic so that the highest level decisions are at the "top of the tree", it can reduce the complexity.  I often use a combination of Select Case statements with If statements embedded in some of the cases.  Then within the If statements, you can include other Select Case statements.

Here's a sample of nesting that might be easier to read than many nested IF statements:

Option Explicit

Public Sub ComplexCarrierLogic()
Dim wkbCarrierData As Workbook
Dim wksCarrierData As Worksheet

Set wkbCarrierData = ActiveWorkbook
Set wksCarrierData = wkbCarrierData.ActiveSheet

Select Case wksCarrierData.Cells(1, 3)
    Case 1
        If wksCarrierData.Cells(1, 5).Value > 5 Then
            Select Case wksCarrierData.Cells(2, 6)
                Case "A"
                    Select Case wksCarrierData.Cells(4, 4)
                        Case "Today"
                            wksCarrierData.Cells(20, 1).Value = "Special Delivery"
                        Case "Tomorrow"
                            wksCarrierData.Cells(20, 1).Value = "USPS"
                        Case "Yesterday"
                            wksCarrierData.Cells(20, 1).Value = "Air Freight"
                    End Select
                Case "B"
                    wksCarrierData.Cells(20, 1).Value = "UPS"
                Case "C"
                    wksCarrierData.Cells(20, 1).Value = "UPS"
            End Select
        Else
            wksCarrierData.Cells(20, 1).Value = "DHL"
        End If
    Case 2
        If wksCarrierData.Cells(1, 5).Value > 20 Then
            Select Case wksCarrierData.Cells(2, 6)
                Case "A"
                    Select Case wksCarrierData.Cells(4, 4)
                        Case "Today"
                            wksCarrierData.Cells(20, 1).Value = "Fedex"
                        Case "Tomorrow"
                            wksCarrierData.Cells(20, 1).Value = "Boat"
                        Case "Yesterday"
                            wksCarrierData.Cells(20, 1).Value = "Truck"
                    End Select
                Case "B"
                ' More Code
                Case "C"
                ' More Code
            End Select
        Else
            wksCarrierData.Cells(20, 1).Value = "Air"
        End If
    Case Else
            wksCarrierData.Cells(20, 1).Value = "Boat"
End Select
End Sub

Regards,

---

Rich Locus, Logicwurks, LLC

http://www.logicwurks.com