The standard IF function is great for handling one condition. Is the sale total greater than $1,000? If yes, apply a discount; if no, don't. But what happens when you need to check multiple conditions in a sequence? For instance, a salesperson's bonus might depend on their performance tier: gold, silver, or bronze.

The traditional way to solve this is by nesting IF statements inside one another. The value_if_false argument of one IF function becomes another complete IF function.

This formula first checks if B2 is "Gold". If it is, the result is 0.1 (10%). If not, it moves to the next IF, which checks for "Silver". If that's true, the result is 0.05 (5%). If both checks fail, it defaults to the final value, 0.02 (2%).

While functional, nesting can get messy. With three or four levels, the formula becomes hard to read, and a single misplaced parenthesis can break the whole thing. It's like a set of Russian dolls, and finding a mistake can be a real headache.

To simplify complex conditional logic, Excel introduced the IFS function. Instead of nesting, IFS lets you list pairs of conditions and their corresponding outcomes in a linear, easy-to-read sequence. It stops at the first condition that evaluates to TRUE.

Let's revisit the bonus calculation. Using IFS, the formula is much cleaner. It's important to include a final "catch-all" condition that is always true, like TRUE, to provide a default value if none of the other conditions are met.

This version does the exact same thing but is far more intuitive. Each condition and its result are a neat pair. There's no deep nesting, making the formula easier to write, read, and debug. For any scenario with more than two outcomes, IFS is almost always the better choice.

Sometimes, your decision doesn't depend on a sequence of checks, but on whether multiple conditions are true at the same time. This is where the AND and OR functions come in. They don't return a value themselves; they return a simple TRUE or FALSE, which you can then use as the logical test inside an IF statement.

Let's say a customer gets a special discount if they are a "Premium" member and their order total is over $100. If the membership status is in C2 and the order total is in D2, you can combine AND with IF.

Now, imagine a shipping promotion where an order gets free two-day shipping if the destination is "Hawaii" or if the order is over $200. With the destination in E2 and the total in D2, the OR function works perfectly.

These functions are the building blocks of precise, multi-faceted decision-making in your spreadsheets. They are based on a fundamental concept from logic and computer science called Boolean logic., annotations: [{"highlightText": "Boolean logic", "annotationText": "## The Logic of True and False\n\nNamed after George Boole, a 19th-century English mathematician.\n\nHe established a system of algebra where variables could only have two values: true or false (or 1 and 0).\n\nThis became the foundational principle for all of digital computing.\n\nEvery complex decision a computer makes, from running an app to processing a spreadsheet formula, boils down to a series of these simple true/false operations."}]

Formulas can sometimes return errors like #N/A (value not available) or #DIV/0! (division by zero). While these errors tell you something is wrong, they look unprofessional and can break other formulas that depend on them. Excel provides functions to gracefully handle these situations.

The IFERROR function is a broad net. It catches any type of error. For example, if you're calculating a ratio with A2/B2 and B2 might be zero, you can wrap it to show a clean message.

This formula attempts the division. If it works, the cell shows the result. If it fails for any reason, the cell shows 0.

For more targeted error handling, especially with lookup functions, there's IFNA. It works just like IFERROR, but it only triggers for the #N/A error. This is useful when you want to catch a failed lookup but still see other errors like #VALUE! or #REF!, which might indicate a different problem with your formula that you need to fix.

Using error-handling functions makes your spreadsheets more robust and user-friendly. Instead of showing cryptic error codes, you can provide clear, custom messages or default values that keep your models running smoothly.