**Using Nested If Statements. **

A reader asked me to explain logic behind the formula used in my Holiday Email post. That solution involved using multiple IF functions as arguments within another IF function, technique also known as **nested ** IF logic. I find that the best way to use nested if functionality is through using decision trees AND Excel’s VBA syntax. Charting a decision tree will help us better understand the task at hand, and putting various conditions and the ways to handle them in writing, will prepare us to enter necessary formula in Excel.

Let’s suppose that we need to implement the following logic with 4 possible scenarios. If the value of cell 1 is string “Case 1” AND cell 2 is string “Case 2”, we need the output to be **4**. If cell 1 equals “Case 1”, but cell 2 does NOT equal “Case 2”, we need the output to be **3**. If cell 1 does NOT equal “Case 1” , but cell 2 equals “Case 2”, the output should be **2**. Finally, if cell 1 does not equal “Case 1”, NOR cell 2 equals “Case 2”, the output is **1**. Hopefully, using the decision tree below makes it easier to conceptualize these scenarios.

We remember that the standard syntax for Excel’s IF function is the following: **=IF(logical_test,[value_if_true],[value_if_false])**, where **Logical_test** is the expression we are trying to evaluate, **Value_if_true** is the value returned if our condition (Logical_test) is TRUE, and **Value_if_false** is the value returned if our condition (Logical_test) is FALSE. As discussed, our *nested * IF statement will have to accept other IF functions as arguments. Putting our scenario in writing (mimicking VBA code), we have:

If logical_test1 Then If logical_test_2 Then value_if_true1 Else: value_if_false_1 End If ElseIf logical_test_3 Then value_if_true2 Else value_if_false2 End If

Both of the above steps should help us to compose our formula:

** =IF(logical_test1,[(IF(logical_test2,[value_if_true1],[value_if_false1]))],[(IF(logical_test3,[value_if_true2],[value_if_false2]))) **.

Let’s try to construct our IF function, assuming that cell **1** is B1 , and cell **2** is B2 .

**logical_test_1 ** is whether cell B1 equals “Case 1”, or: B1=”Case 1″

**logical_test_2 ** is B2=”Case 2″

**value_if_true1 ** is 4

**value_if_false1 ** is 3

**logical_test_3 ** is B2=”Case 2″

**value_if_true2 ** is 2

**value_if_false2 ** is 1

Making all of the necessary substitutions, our master formula becomes:

**=IF(B1=”Case 1″,IF(B2=”Case 2″,4,3),IF(B2 = “Case 2”,2,1))**

Testing for all possible scenarios we should get results as expected:

We are now prepared to tackle our real-world problem. Decision tree that started this post demonstrates 4 possible scenarios that we have to accommodate, contingent on whether the user specifies a *CC * email address or *BCC * email address; both of these addresses, or none of them:

**Scenario 1** If both addresses are available, we need to carbon copy the *CC * address and blind carbon copy the *BCC * address in our email.

**Scenario 2** If only *CC * address is available, we need to carbon copy the *CC * address in our email.

**Scenario 3** If only *BCC * address is available, we need to blind carbon copy the *BCC * address in our email.

**Scenario 4** If neither address is available, we won’t be using neither *BCC * , nor *BCC * fields in our email.

We know that a valid email address is at least 6 characters long (a@1.us) Let’s assume that having the CC field (cell **C5**) with less than 6 characters long, the CC address is not available; similarly BCC address is not available, if our BCC field (cell **C6**) is less than 6 characters long. To help us evaluate number of characters in these cells, we will use Excel’s ** LEN ** function. Knowing that the syntax for our email HYPERLINK logic, using the *To *, *Subject *, and *Body * fields only is:

**=HYPERLINK(“mailto:”&C4&”?subject=”&C7&”&body=”&SUBSTITUTE(C8,CHAR(10),”%0D%0A”),C7)**,

we can construct 4 different scenarios described:

'This is NOT actual VBA code; it is an attempt to explain logic for our holiday email formula If LEN(C5)>5 Then If LEN(C6)>5 Then 'Scenario 1 - Both: CC and BCC addresses are available =HYPERLINK("mailto:"&C4&"?subject="&C7&"&body="&SUBSTITUTE(C8,CHAR(10),"%0D%0A")&"&CC="&C5&"&BCC="&C6,C7) Else: 'Scenario 2 - Only CC address is available =HYPERLINK("mailto:"&C4&"?subject="&C7&"&body="&SUBSTITUTE(C8,CHAR(10),"%0D%0A")&"&CC="&C5,C7)) End If ElseIf If LEN(C6)>5 Then 'Scenario 3 - Only BCC address is available =HYPERLINK("mailto:"&C4&"?subject="&C7&"&body="&SUBSTITUTE(C8,CHAR(10),"%0D%0A")&"&BCC="&C6,C7) Else 'Scenario 4 - Neither CC, nor BCC address is available =HYPERLINK("mailto:"&C4&"?subject="&C7&"&body="&SUBSTITUTE(C8,CHAR(10),"%0D%0A"),C7) End If

Our master formula looks as following:

** =IF(LEN(C5)>5,IF(LEN(C6)>5,HYPERLINK(“mailto:”&C4&”?subject=”&C7&”&body=”&SUBSTITUTE(C8,CHAR(10),”%0D%0A”)&”&CC=”&C5&”&BCC=”&C6,C7),HYPERLINK(“mailto:”&C4&”?subject=”&C7&”&body=”&SUBSTITUTE(C8,CHAR(10),”%0D%0A”)&”&CC=”&C5,C7)),IF(LEN(C6)>5,HYPERLINK(“mailto:”&C4&”?subject=”&C7&”&body=”&SUBSTITUTE(C8,CHAR(10),”%0D%0A”)&”&BCC=”&C6,C7),HYPERLINK(“mailto:”&C4&”?subject=”&C7&”&body=”&SUBSTITUTE(C8,CHAR(10),”%0D%0A”),C7)))**

If parentheses are giving you any trouble, I highly recommend using Excel’s formula wizard to construct your formula. I hope this explanation has helped you better understand Excel’s nested IF logic, please let me know if I need to clarify this concept a bit further.