Finally, you can also use the IFS function in later versions of Excel to reduce nesting. You could also replace AND and OR with boolean logic. The AND function can handle more logical tests, and you can combine the AND function with the OR function if needed. This formula approach can be expanded to evaluate more options. You'll need to expand the formula bar vertically in order to see more than one line at a time.
Note can use use Alt + Enter to enter to enter new lines in the formula bar. The AND function inside each IF function tests both color and value. To limit IF functions, we are using the AND function to run more than one logical test at a time. Notice the final false result will 'catch' any case that fails all previous tests.Įssentially, we are using line breaks to build a 'table'that the human eye can easily read. The 'false result' is the following IF statement. The trick is to structure the formula with line breaks to show each IF on a separate line along with the 'true result' for that IF. The formula in this example is purposely more verbose that necessary in order to 'show' all possible options and results in a way that is easier to understand and maintain. With nested IFs, one IF function is nested inside another, a process that is explained in some detail here. This formula relies on a technique called 'nested IFs' to handle a series of options and results.
