Excel Practice Sheets

IF Formula Generator

Describe the test and what to show for true and false, and get the IF formula — including the AND and OR combinations people usually get wrong.

What to check, such as B2>=50 or C2="East".

Shown when the test passes. Text is quoted for you.

Shown when the test fails.

When to use IF

  • Labelling rows as pass or fail, over or under, flagged or clear
  • Showing something readable in a report instead of a raw number
  • Two conditions that both have to hold, or either one of them

How the formula works

Taking the example on this page apart, piece by piece:

=IF(B2>=50,"Pass","Fail")

  1. B2>=50

    The test. Excel checks whether B2>=50 is true.

  2. "Pass"

    Returned when the test is true. Text goes in quotes; numbers and cell references do not.

  3. "Fail"

    Returned when the test is false.

Where it goes wrong

  • Nesting IFs when the bands are ordered

    Four nested IFs still work, but they are hard to read and easy to get out of order. Once the tiers are ranges rather than cases, IFS or a lookup table is clearer.

  • Quoting numbers

    Returning a quoted 1 rather than a bare 1 gives you text that looks like a number, and the total below it silently ignores it.

  • Assuming an empty-looking cell is blank

    A cell holding a formula that returns an empty string is not blank. ISBLANK and a comparison against empty text disagree about it, which is where the confusion starts.

Practise it

A generator gives you the formula. These give you the reason it works — and tell you which assumption broke when it does not.

Questions

When should I use IFS instead of IF?

When there are more than two outcomes. IFS takes condition and result pairs in order and stops at the first match, which reads better than four nested IFs. It needs Excel 2019 or Microsoft 365, and it returns #N/A when nothing matches — so finish it with TRUE and a fallback.

How do I test two things at once?

Wrap them in AND when both must be true, or OR when either will do. Both go inside the first argument of IF, so the shape stays the same.

Why does my IF return the wrong branch?

Usually the comparison rather than the IF. Text comparisons ignore case but not spaces, and a number stored as text never equals the same number. Put the condition in its own cell and check whether it returns TRUE.
Practise IF Learn IF