Beyond basic arithmetic, Excel's true power comes from its ability to make decisions. You can teach your spreadsheets to analyze data and perform different actions based on the conditions you set. This is done using logical functions, which essentially follow a simple structure: if this is true, then do that; otherwise, do something else.

The cornerstone of this logic is the IF function. It evaluates a condition you provide and returns one value if the condition is true and another if it's false.

Let's break that down:

• logical_test: This is the condition Excel checks. For example, is the value in cell B2 greater than 100? This test must resolve to either TRUE or FALSE.
• value_if_true: What to display if the test is TRUE.
• value_if_false: What to display if the test is FALSE.

Imagine you have a list of invoices and want to quickly see which ones are overdue. If a payment is due in 30 days, any invoice older than that is late. We can use comparison operators like the greater-than symbol (>) to build our test.

In cell C2, you would enter the following formula and drag it down the column:

This formula tells Excel: Check if the number in cell B2 is greater than 30. If it is, display the text "Late". If it's not, display "OK".

But what if you need to check for more than just two outcomes? For example, a grading system doesn't just have "Pass" and "Fail"; it has A, B, C, D, and F. This is where you can either nest IF functions or use the more modern IFS function.

Nesting IF functions means placing an IF function inside another one, usually in the value_if_false position. It's like a series of branching questions.

To assign grades based on the score in column A, you could use a nested IF formula like this:

This works, but it can get complicated and hard to read. Each IF needs its own set of parentheses, and keeping track of them is a common source of errors. For this reason, Microsoft introduced the IFS function for users of Excel 2019 and newer versions.

The IFS function allows you to test multiple conditions in a cleaner, more straightforward way. You list your tests and their corresponding true values in pairs.

The logic is identical, but the structure is much simpler. You just list each test followed by its result, one after the other.

Sometimes a single test isn't enough. You might need to check if multiple conditions are met at the same time, or if at least one of several conditions is met. For this, you can combine the AND and OR functions within your IF statement.

Let's create an automated inventory reorder alert. We need to reorder a product if its stock is low (e.g., under 20 units) AND it is not already on order. We can use the AND function inside our IF test.

In cell D2, the formula would be:

This formula checks two things for each product: Is the stock in column B less than 20? AND is the value in column C equal to "NO"? Only if both conditions are met will it return "Reorder"; otherwise, it returns "OK".

Mastering these logical functions is a key step in moving from simply storing data in Excel to actively analyzing it and automating decisions.