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.