You're already familiar with the basic IF function, which checks a condition and returns one value if it's true and another if it's false. But real-world data is rarely that simple. What if you have three, four, or even five possible outcomes?

This is where nesting IF statements comes in. You can place an IF function inside another IF function to create a decision-making chain. This lets you test for multiple conditions in a sequence.

In this formula, Excel first checks if the sale in cell A2 is greater than ₹1,00,000. If it is, it calculates a 10% commission. If not, it moves to the second IF function. This one checks if the sale is greater than ₹50,000. If true, it calculates a 5% commission. If that's also false, it returns 0. The chain stops as soon as a condition is met.

Nesting IFs works, but it can get complicated and hard to read. A cleaner approach for testing multiple criteria within a single condition is to use the AND, OR, and NOT functions. They return a simple TRUE or FALSE, which you can then plug into an IF statement's logical test.

AND checks if all conditions are met. For a condition to be TRUE, every single part of it must be true.

OR checks if any condition is met. For a condition to be TRUE, only one of its parts needs to be true.

NOT simply reverses the outcome. It turns TRUE into FALSE and FALSE into TRUE.

Let's apply this. Suppose a bonus is only paid to employees in the 'Sales' department who exceeded their target of ₹2,50,000. Using AND makes this check straightforward.

This formula is much easier to read than a nested IF. It checks both conditions at once and returns "Bonus" only if the employee is in Sales and their sales are over ₹2,50,000.

Errors like #N/A, #VALUE!, or #DIV/0! can make a spreadsheet look unprofessional and break further calculations. Instead of letting them appear, you can catch them and display something more useful, like a zero, a blank cell, or a custom message.

The IFERROR function is a broad tool for this. It wraps around your original formula and provides a backup value to show if any error occurs.

Here, if the VLOOKUP works, the formula returns the price. If it fails for any reason and produces an error, the formula returns the text "Not Found" instead.

A more specific function is IFNA. It works just like IFERROR, but it only catches the #N/A (Not Available) error. This is useful when you specifically want to handle lookup failures but still want to see other potential errors, like a #REF! error if your table range gets deleted.

This version returns a 0 if the product ID isn't found, which might be better for subsequent calculations than a text message. If any other error occurs, IFNA will let it show through.

Logical functions are not just for calculating values in cells. You can also use them to drive Conditional Formatting, changing a cell's appearance based on a TRUE/FALSE outcome. This makes your data much easier to interpret at a glance.

The key is to create a formatting rule based on a formula. Excel applies the formatting to any cell where your formula evaluates to TRUE.

For example, let's highlight all rows where an invoice is more than 30 days overdue and the amount is over ₹10,000. Assume the due date is in column C and the amount is in column D.

1. Select the data range you want to format (e.g., A2:D100).
2. Go to Conditional Formatting and choose 'New Rule'.
3. Select 'Use a formula to determine which cells to format'.
4. Enter the formula. Remember to use absolute and relative references correctly so the formula adjusts for each row.

Notice the dollar signs ($) locking the column references. This ensures that for every cell in the selected range, Excel looks at columns C and D for that specific row. If both conditions are met, the formula is TRUE, and Excel applies the format (like a red fill) you've chosen.

By combining these logical tools, you can build dynamic, error-resistant spreadsheets that automate complex decisions and present data clearly.