Conditional, or “if-then” statements, are an incredibly powerful way to get Excel to do work for you. Writing them does require up-front investment, but the return on that time and energy spent is significant. In this post we will show you how to “nest” one conditional statement formula inside another to streamline your spreadsheet’s reporting. First, some foundations.
What is a conditional statement?
Conditional statements, which some people just refer to as “IF statements”, are formulas that employ the IF function, whose generic syntax is: =IF(criterion,x,y)
The IF function conducts a logic test to see if the stated criterion (test) is met, and if so, then action x is carried out (or value x is returned), and if not, action y is carried out (or value y is returned). So one IF function provides for two possible outcomes.
An example of an IF statement is the following that could sit in the “Residences Sold” line of a condominium development pro-forma:
=IF(current month # is <= month in which pre-sales end,return the average number of pre-sales units sold per month,return a 0)
This statement assumes that you have a monthly-based projection and tests to see if the current month is before or equal to the month in which the defined pre-sales period terminates. If so, the variable for units sold per month during pre-sales is returned to the cell in which the formula sits. Otherwise (if the month is beyond the month of pre-sales end), a zero will be returned to the cell instead.
What does it mean to “nest” conditional statements, and how do you do it?
The IF statement above works perfectly well, but what if there are three potential outcomes for which we want to account, instead of only two?
Extending the above example, we will naturally want to also include in the “Residences Sold” line those sold during the regular sales (non-pre-sales) period, for which we will have a variable defined for the start month #. To accomplish the modeling of these regular sales, we can use the following logic:
=IF(current month # is >= month in which regular sales start,return the average number of regular sales units sold per month,return a 0)
The question is, given the pre-sales and regular sales periods do not overlap, how do you combine these two tests into a single statement that will provide for the three potential outcomes for sales in any single month:
- pre-sales average amount sold
- regular sales average amount sold
- none sold.
This is done through what is known as “nesting” one of the logic tests within the other. Here’s how to do it:
=IF(current month # is <= month in which pre-sales end,return the average number of pre-sales units sold per month,IF(current month # is >= month in which regular sales start,return the average number of regular sales units sold per month,return a 0)) (Note the two close parentheses required since there are two IF functions being used)
The way you read this logic is:
If the current month is a pre-sales month, return average sales amount for pre-sales,
If the current month is a regular sales month, return average sales amount for regular sales,
Otherwise, return 0.
This example is shown in the embedded spreadsheet below, which you will need to download to be able to expose the formulas so that the component parts become color-coded to match the colors of the arguments in the formulas. Enjoy and please post questions below.