Mar 17, 2014

However, there are times where such a simple IF statement doesn’t cover every possibility. In the example above, the formula would result in “loss” if cell A1 contained a negative number, but also if it contained zero. A zero would indicate that they broke even; it’s not a loss. To have an accurate reflection of this situation, we can enhance the IF function with nested IF functions.

How to use nested IF functions

To overcome the above problem, a Nested IF Function can be used to test a second condition. The above formula needs to first test to see if A1 has a positive number, but if it doesn’t, it then has to test to see if it has a negative number or not. If not, A1 must contain zero, so therefore they have broken even:

This formula can read as saying: “If A1 is greater than zero, there is a profit. However, if A1 is a less than zero, there is a loss. If it’s neither of these cases, there is a break even.” If there were a fourth possibility, there could be a nested IF statement within the nested IF statement or there could nested IF statements in both the true and false parts of the original IF formula. For example: This formula can be read as saying: “If A1 is greater than zero then check if it’s more than 100 and if it is, then there’s a “big profit” and if not, there there’s a “small profit.” However, if A1 is less than zero, there’s a “loss” and if it’s not less than zero, there’s a “break even.” If you’d like to learn more about these functions, you should take a look at New Horizons’ Microsoft Excel training programs, and in particular Excel 2010 Level 2 and Excel 2013 Level 2.