TheIF functionis one of the most heavily used functions in Excel.

With IF, you’re free to bring your spreadsheet to life.

Are they sometimes necessary?

Basic IF function - return “Pass” for scores of at least 65

What are the alternatives?

Read on to learn the answers to these questions and more…

1.

Note the square brackets…these mean the arguments are optional.

Basic IF function - return “Pass” for scores of at least 65

However, you must supplyeithera value for true, or a value for false.

The MONTH function takes that date and returns the current month.

Even moderately complex formulas use nesting frequently, so you’ll see nesting everywhere in more complex formulas.

Basic IF function - return “Pass” for scores of at least 65

If so, outer IF returns “Incomplete”, and the inner IF never runs.

If the score isnot blank, the outer IF returns FALSE, and the original IF function is run.

Then, instead of adding another IF, just add the final grade for false.

Basic IF function - return “Pass” for scores of at least 65

This means that results from outer IFs determine whether inner IFs even run.

The diagram below visualizes the logical flow of the grade formula above.

The screen below shows the Evaluate window open and ready to go.

Basic IF function - return “Pass” for scores of at least 65

Each time you hit the Evaluate button, the “next step” in the formula is solved.

you might find Evaluate on the Formulas tab of the ribbon (Alt M, V).

This is a powerful way to confirm what a formula is really doing.

Basic IF function - with a value added for false

you’re able to also press Esc to exit the formula editor without any changes.

Video:How to debug a formula with F9

8.

Know your limits

Excel has limits on how deeply you could nest IF functions.

A basic nested IF

Up to Excel 2007, Excel allowed up to 7 levels of nested IFs.

In Excel 2007+, Excel allows up to 64 levels.

However, just because youcannest a lot of IFs, it doesn’t mean youshould.

Completed nested IF example for calculating grades

Every additional level you add makes the formula more difficult to understand and troubleshoot.

Match parentheses like a pro

One of the challenges with nested IFs is matching or “balancing” parentheses.

When parentheses aren’t matched correctly, your formula is broken.

The logical flow of a nested IF

If there is no match, you’ll see no bolding.

Unfortunately, the bolding is a Windows-only feature.

you’ve got the option to paste the formula back into Excel after you’ve straightened things out.

Using Evaluate to step through a nested IF that assigns grades

Then don’t use quotes around the number.

If the test score is numeric, use this:

12.

Because Excel doesn’t care about “white space” in formulas (i.e.

Using F9 check a nested IF that assigns grades

Video:How to make a nested IF easier to read.

One way to avoid more levels is to use IF in combination with the AND and OR functions.

Video:IF this OR that

14.

Formula parentheses are color-matched but hard to see

Although the formula above uses exact matching, it’s possible for you to easily useVLOOKUP for gradesas well.

For example, suppose you have a calculation that needs to result in a positive number, or zero.

In other words, if the calculation returns a negative number, you just want to show zero.

Navigate and select formula arguments with the screen tip

I love this construction because itsjust so simple.See this article for a full write-up.

Use boolean logic

you could also sometimes avoid nested IFs by using what’s called “boolean logic”.

The word boolean refers to TRUE/FALSE values.

Nested IFs without line breaks are difficult to read

you’re free to use this fact to write clever, and very fast formulas.

For numeric results, boolean logic is simple and extremely fast, since there is no branching.

On the downside, boolean logic can be confusing to people who aren’t used to seeing it.

Line breaks make nested IFs easier to read

Still, it’s a great technique to know about.

Video:How to use boolean logic in Excel formulas

When do you need a nested IF?

Your thoughts?

IF with the AND function is simpler than two nested IFs

Are you an IF-ster?

Do you avoid nested IFs?

Are nested IFs evil?

Nested IF vs VLOOKUP

Share your thoughts below.

Nested IF vs the CHOOSE function

The IFS function - multiple conditions without nesting

Calculating invoice status with a nested IF

Basic IF function - return “Pass” for scores of at least 65

Basic IF function - with a value added for false

A basic nested IF

Completed nested IF example for calculating grades

Using Evaluate to step through a nested IF that assigns grades

Using F9 check a nested IF that assigns grades

Formula parentheses are color-matched but hard to see

Navigate and select formula arguments with the screen tip

Nested IFs without line breaks are difficult to read

Line breaks make nested IFs easier to read

IF with the AND function is simpler than two nested IFs

Nested IF vs VLOOKUP

Nested IF vs the CHOOSE function

The IFS function - multiple conditions without nesting

Calculating invoice status with a nested IF