Beyond basic arithmetic, Excel's real power comes from its ability to make decisions. The cornerstone of this is the IF function. It checks if a condition is true or false and then returns a value you specify for each outcome.

Think of it as a simple question: Is this condition met? If yes, do this. If no, do that.

Here’s a breakdown:

• logical_test: The condition you want to check. For example, A2 > 50.
• value_if_true: What to show if the condition is met.
• value_if_false: What to show if the condition is not met.

Let's say you have a list of student scores in column A. You want to label anyone with a score of 70 or higher as "Pass" and everyone else as "Fail". In cell B2, you would write:

When you drag this formula down, it will automatically evaluate each student's score and assign the correct label. It's a simple but powerful way to start categorizing your data.

What if you need more than two outcomes? For instance, instead of just "Pass" or "Fail," you want to assign letter grades: A for scores over 90, B for 80-89, and C for everything else.

This is where you can nest IF functions. A nested IF is an IF function placed inside another IF function, usually in the value_if_false argument. It lets you create a chain of logical checks.

Let's trace the logic for a score of 85:

1. Is 85 >= 90? No. So, Excel moves to the value_if_false part.
2. The value_if_false is another IF function: IF(A2>=80, "B", "C").
3. Is 85 >= 80? Yes. Excel returns "B".

If the score was 75, it would fail both the first and second tests, so it would return the final value_if_false, which is "C".

Nesting IFs is useful, but sometimes the complexity isn't in the number of outcomes, but in the condition itself. To build more nuanced logical tests, you can combine IF with three other functions: AND, OR, and NOT.

These functions are typically placed inside the logical_test part of an IF statement.

Imagine you want to flag employees for a bonus. The criteria are: sales greater than $50,000 (in cell B2) AND they must have attended a training session (cell C2 says "Yes").

Here, both conditions must be met to get the "Bonus Eligible" label. If an employee had $60,000 in sales but didn't attend training, they wouldn't qualify.

Now, let's say a project is considered "At Risk" if its budget is over 90% spent (D2) OR it's past its due date (E2).

With this formula, a project is flagged if either condition is true. It doesn't need to meet both.

Real-world data is rarely perfect. You'll often deal with blank cells, errors, or unexpected text. Logical functions are excellent for cleaning this up and making your formulas more resilient.

A common problem is a formula that returns an error because it's trying to calculate with a blank cell. You can use the ISBLANK function within an IF to prevent this.

Suppose you are calculating a 5% commission in column C based on sales figures in column B. If a cell in column B is empty, the formula might return a 0 or an error. You can leave the commission cell blank instead.

This formula first checks if B2 is empty. If it is, it returns an empty string "", making your sheet look clean. If B2 is not blank, it proceeds with the calculation B2*0.05.

You can also combine this with OR to handle multiple types of messy data. For example, you want to flag a row for review if a cell contains an error or is blank.

By building these checks into your formulas, you create spreadsheets that are more robust and less likely to break when new or imperfect data is added.

Mastering these logical functions moves you from simply calculating with data to actually analyzing and making decisions with it.