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. 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 “false result” is the following IF statement. Notice the final false result will “catch” any case that fails all previous tests. Essentially, we are using line breaks to build a “table"that the human eye can easily read. To limit IF functions, we are using the AND function to run more than one logical test at a time. The AND function inside each IF function tests both color and value. Note can use use Alt + Enter to enter to enter new lines in the formula bar. You’ll need to expand the formula bar vertically in order to see more than one line at a time.
More conditions
This formula approach can be expanded to evaluate more options. The AND function can handle more logical tests, and you can combine the AND function with the OR function if needed. You could also replace AND and OR with boolean logic. Finally, you can also use the IFS function in later versions of Excel to reduce nesting.
Result as calculation
Although the example above shows a numeric result for each set of options, the formula can be customized to run a calculation instead by replacing hardcoded values with any standard formula expression.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
