Top 12 Excel Combinations: 12. IF IF (Nested IFs)

It’s a new year and I thought I would use 2024 as an opportunity to count down a very subjective “Top 12” of Excel function combinations. Yup, I’ve come up with a very loose theme for the new year! It did get me thinking though…

Starting the series off and certain 12th of 12, is a practice that I deplore personally – multiple IF statements. Yuck. However, every Excel user sees them all the time and it would be remiss of me if I didn’t include this combo in my list as much as I would like to metaphorically nail authors of such monsters to a wall to make an example of them.

It’s not that there aren’t occasions when you need to consider multiple conditions. It’s just that IF(IF(IF… ad nauseum is just not well structured and can make things more difficult to understand, providing ample opportunities for errors to be introduced into spreadsheets / models.

Let’s start by introducing IF: it’s a very important function (that’s what IF stands for, Important Function) [no it doesn’t – Ed.]. Its syntax is as follows:

=IF(logical_test, [value_if_TRUE], [value_if_FALSE])

This function has three [3] arguments:

• logical_test: this is the “decider”, that is, a test that results in a value of either TRUE or FALSE. Strictly speaking, the logical_test tests whether something is TRUE; if not, it is FALSE
• value_if_TRUE: what to do if the logical_test is TRUE. Note that you do not put square brackets around this argument. This is just the Excel syntax for saying sometimes this argument is optional. If this argument is indeed omitted, this argument will have a default value of TRUE
• value_if_FALSE: what to do if the logical_test is FALSE (not TRUE). If this argument is left blank, this argument will have a default value of FALSE.

As you can clearly see, it only allows for one logical_test. Therefore, what do you do when you need to test for multiple conditions? If you’re thinking

=IF(IF(IF…

then maybe the first alternative I mention is for you – however, if you have ever written Excel formulae like this, then maybe Excel isn’t for you! There are usually better ways of writing the formula using other functions.

There is a relatively new function IFS. The syntax for IFS is as follows:

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)

where:

• logical_test1 is a logical condition that evaluates to TRUE or FALSE
• value_if_true1 is the result to be returned if logical_test1 evaluates to TRUE. This may be empty
• logical_test2 (and onwards) are further conditions that evaluate to TRUE or FALSE also
• value_if_true2 (and onwards) are the respective results to be returned if the corresponding logical_test evaluates to TRUE. Any or all may be empty.

Since functions are limited to 254 arguments (sometimes known as parameters), the new IFS function can contain 127 pairs of conditions and results.

One thing to note is that IFS is not quite the same as IF. With the IF statement, the third argument corresponds to what do if the logical_test is not TRUE (that is, it is an ELSE condition). IFS does not have an inherent ELSE condition, but it can be easily generated. All you have to do is make the final logical_test equal to a condition which is always true such as TRUE or 1=1 (say).

You should also note:

• whilst the value_if_true may be empty, it must not be omitted. Having an odd number of arguments in an IFS statement would give rise to the “You’ve entered too few arguments for this function” error message
• if a logical_test is not actually a logical test (for example, it evaluates to something other than TRUE or FALSE, the function returns an #VALUE! error. Numbers still appear to work though: any number than zero evaluates as TRUE and zero is considered to be FALSE
• if no TRUE conditions are found, this function returns the #N/A error.

To show how it works, consider the following example:

Here, would-be gurus are graded based on evaluation criteria in the table, applied in a particular order:

=IFS(H13="Yes",I13,H14="Yes",I14,H15="Yes",I15,H16="Yes",I16,TRUE,"Not a Guru")

I think it’s safe that although it is reasonably straightforward to follow, it is entirely reasonable to say it’s not the prettiest, most elegant formula ever put to Excel paper. In particular, do pay heed to the final logical_test: TRUE. This ensures we have an ELSE condition as discussed above.

To be fair, one similar solution using previous Excel functions isn’t any better:

=IF(H13="Yes",I13,IF(H14="Yes",I14,IF(H15="Yes",I15,IF(H16="Yes",I16,"Not a Guru")))).

You may note I am not supplying multiple examples of IFS formulae. This is because wherever possible you should try and replace the logic with a simpler, more accessible, logic for end users. For instance, sometimes the logic of an elongated IF or IFS formula may be condensed to

=IF(Multiple Conditions = TRUE, Do Something, Do Something Else).

In this situation, there is a function in Excel that can help.

My old English teacher said you should never start or finish a sentence with the word “and”. AND is one of several Excel logic functions (others include NOT [already mentioned earlier, which takes the logical opposite of an expression] and OR). It returns TRUE if all of its arguments evaluate to TRUE; it returns FALSE if one or more arguments evaluate to FALSE.

One common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the AND function as the logical_test argument of the IF function, you can test many different conditions instead of just one.

For example, imagine you are in New York on a Monday. Consider the expression

=AND(condition1, condition2, condition3)

where:

• condition1 is the condition, “the year is 2024”
• condition2 is the condition, “you are in New York” and
• condition3 is the condition, “this author is the best looking guy you have ever seen”.

This would clearly be FALSE as not everywhere in the universe is it 2024…

As alluded to above, the syntax for AND is as follows:

AND(logical1, [logical2], …)

where:

• logical1: the first condition that you want to test that can evaluate to either TRUE or FALSE
• logical2: additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions. logical2 is optional and is not needed in the syntax.

It should be noted that:

• the arguments must evaluate to logical values, such as TRUE or FALSE, or the arguments must be arrays or references that contain logical values
• if an array or reference argument contains text or empty cells, those values are ignored
• if the specified range contains no logical values, the AND function returns the #VALUE! error value.

To highlight how AND works:

For a more practical example, consider the following summary data table:

Here, we have a list of staff in column A, with identification of those who work in Sales (that is, eligible for a bonus) in column B. Details of the sales made, the threshold for getting a bonus, and what rate it is paid are detailed in columns C, D, and E respectively. The formula in cell F2:

=IF(AND(B2="yes",C2-D2>=0),C2*E2,)

denotes the Bonus Paid and is conditional on them working in Sales (B2="yes") and that the sales made were at or above the required threshold (C2-D2>=0). If both conditions are TRUE, then a bonus (C2*E2) is paid accordingly (putting nothing after the final comma is the equivalent of saying “else zero”). This is a prime example of IF and working together – and more often than not, these formulas are much easier to read than their IF(IF(IF or IFS counterparts.

The other logic function not yet mentioned, OR, is similar to AND, but only requires one condition to be TRUE. Similar to AND, the OR function may be used to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the OR function as the logical_test argument of the IF function, you can test many different conditions instead of just one.

The syntax for OR is as follows:

OR(logical1, [logical2], …)

where:

• logical1: the first condition that you want to test that can evaluate to either TRUE or FALSE
• logical2: additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.  is optional and is not needed in the syntax.

It should be noted that:

• the arguments must evaluate to logical values, such as TRUE or FALSE, or the arguments must be arrays or references that contain logical values
• if an array or reference argument contains text or empty cells, those values are ignored
• if the specified range contains no logical values, the OR function returns the #VALUE! error value.

In summary, OR works as follows:

For a more practical example, consider the following summary data table:

Now there is a complex formula:

=IF(OR(AND(B2=”yes”,C2-D2>=0),AND(B2<>”yes”,C2-$C$13>=0)),C2*IF(B2=”yes”,E2,$C$15),)

It isn’t quite as bad as it first seems. This is based on the AND case study from earlier, but it also allows for Non-Sales staff to participate in the bonus scheme too. The logical_test in the primary IF statement,

OR(AND(B2=”yes”,C2-D2>=0),AND(B2<>”yes”,C2-$C$13>=0))

Is essentially OR(condition1, condition2). The first condition is as before for Sales staff, whereas the second,

AND(B2<>”yes”,C2-$C$13>=0)

checks whether Non-Sales staff have exceeded the Non-Sales Staff threshold (cell C13). Do you see that the check for Non-Sales staff is given by B2<>”yes” (B2 is not equal to ”yes”) rather than B2=”no”? This takes me back to my earlier point about ensuring you develop your logical_test correctly. It’s a subtle point, but will ensure all staff are considered (rather than excluding staff where no entry has been made in column B).

The other IF statement,

IF(B2=”yes”,E2,$C$15)

simply ensures the correct bonus rate is applied to the sales figure.

To summarise so far, sometimes your logical_test might consist of multiple criteria:

=IF(condition1=TRUE,IF(condition2=TRUE,IF(condition3=TRUE,formula,),),)

Here, this formula only gives a value of 1 if all three conditions are true. This nested IF statement may be avoided using the logical function AND(Condition1,Condition2,…) which is only TRUE if and only if all dependent arguments are TRUE,

=IF(AND(condition1,condition2,condition3),formula,)

which is actually easier to read. A similar example may be constructed for OR also. However, even using these logic functions, formulas may become – or simply look – complex quite quickly. There is an alternative: flags. In its most common form, flags are evaluated as

=(condition=TRUE)*1

condition=TRUE will give rise to a value of either TRUE or FALSE. The brackets will ensure this is condition is evaluated first; multiplying by 1 will provide an end result of zero (if FALSE, as FALSE*1 = 0) or one (if TRUE, TRUE*1 = 1). I know some modellers prefer TRUEs and FALSEs everywhere, but I think 1’s and 0’s are easier to read (when there are lots of them) and more importantly, easier to sum when you need to know how many issues there are.

Flags make it easier to follow the tested conditions. Consider the following:

In this illustration, you might not understand what the MOD function does, but hopefully, you can follow each of the flags in rows 4 to 7 without being an Excel guru. Row 9, the product, simply multiplies all of the flags together (using the PRODUCT function allows you to add additional conditions / rows easily). This effectively produces a sophisticated AND flag, where all of the formulas are mercifully short. If I wanted the flag to be a 1 as long as one of the above conditions is TRUE (that is, I wish to construct an OR equivalent), that is easy too:

Flags frequently make models more transparent and this example provides a great learning point. Often, we mistakenly believe that condensing a model into fewer cells makes it more efficient and easier follow. On the contrary, it is usually better to step out a calculation. If it can be followed on a piece of paper (without access to the formula bar), then more people will follow it. If more can follow the model logic, errors will be more easily spotted. When this occurs, a model becomes trusted and therefore is of more value in decision-making.

Ne careful though. Sometimes you just can’t use flags. Consider the following instance:

Here, the flag does not trap the division by zero error. This is because this formula evaluates to

=#DIV/0! x 0

which equals #DIV/0! If you need to trap an error, you must use an IF function.

Word to the Wise

I could make this article extremely long [please don’t – Ed.], but there are other functions you could consider too. I conclude by summarising several, albeit briefly, that you might wish to review further:

SWITCH(expression, value1, result1[, value2, result2, …])
This function evaluates a single expression against multiple cases, returning the corresponding result. It offers a concise and readable way to handle multiple cases and is ideal for scenarios with mutually exclusive outcomes, e.g.

=SWITCH(A2, >=90, "A", >=80, "B", >=70, "C", "<70", "D")

CHOOSE(index_number, value1[, value2, …])
Similar to SWITCH, this function lets you pick from predefined values based on a condition. It’s useful for predefined options or lookup tables and it is simple and efficient for straightforward conditional choices, e.g.

=CHOOSE(MATCH(A2, {90, 80, 70}), "A", "B", "C", "D")

INDEX(reference, match_number) and MATCH(lookup_value, lookup_vector, match_type)
You may combine INDEX and MATCH dynamically to select values based upon your criteria. It provides dynamic lookup capabilities for more complex scenarios. This combination is powerful and adaptable for complex lookups and matching operations, as it can handle various data structures and relationships. For example, imagine you had set up a table with scores and corresponding grades (e.g. 90-100 = A, 80-89 = B, …). If you assume scores are in A2:A5 and grades and grades are in B2:B5, the following formula could be used:

=INDEX(B2:B5, MATCH(A2, A2:A5, 1)).