IF, AND, OR and IFERROR: Making Excel Decide

Excel for Jobs: Formulas, Pivot Tables and Office Skills · Formulas · 4 min read · Updated

A formula that decides

  1. IF starts with a test, such as: is the amount 2000 or more?
  2. If the test is true, the formula returns the first result, for example the word Large.
  3. If the test is false, it returns the second result, for example Small.
  4. Copy the formula down and every row makes its own decision.

So far Excel has only calculated. Now it will decide. The IF function lets a formula choose between two results depending on a test, for example “if the sale is big, show Large, otherwise show Small”. Office jobs use IF all the time: pass or fail, late or on time, bonus or no bonus, stock low or okay. This lesson teaches it clearly, with the other logic functions that go with it.

The IF function

=IF(test, value_if_true, value_if_false)

The test is a comparison that is either true or false. You build it with the signs =, <> (not equal), >, <, >= and <=. In the sales table, add a heading Size in column L (leave I to K for the tax rate from lesson 3) and type in L2:

=IF(G2>=2000, "Large", "Small")

Row 2 has an amount of 1200, which is less than 2000, so it shows Small. Copy it down. Rows with amounts 2000, 2800, 2800, 2400 and 2400 show Large, and the rest show Small. Text inside a formula goes in double quotes. Numbers do not.

Using cell references in the test

Never hide the threshold inside a formula where nobody can see it. Put 2000 in a labelled cell, say O1 with the label Large from in N1, and write =IF(G2>=$O$1, "Large", "Small"). Now a manager can change the limit and the whole column updates.

Calculating inside IF

The results can be calculations, not just words. A shop gives a 10% discount on amounts of 2000 or more:

=IF(G2>=2000, G2*0.9, G2)

With G2 = 2000 the result is 1800. With 1200 it stays 1200. Again, a real sheet should keep the 0.9 or the 10% in a labelled cell.

Testing text

=IF(C2="North", "Head office", "Branch")

Text comparisons in Excel ignore upper and lower case, so "north" also matches. Spelling and extra spaces matter, so “North ” with a trailing space will not equal “North”. Lesson 8 shows how to remove stray spaces.

AND and OR: more than one condition

  • AND(test1, test2) is true only if all the tests are true.
  • OR(test1, test2) is true if any of the tests is true.
  • NOT(test) reverses a test.

They are used inside IF. For example, a bonus is paid if the sale is at least 2000 and the region is North:

=IF(AND(G2>=2000, C2="North"), "Bonus", "No bonus")
=IF(OR(C2="North", C2="East"), "Priority", "Normal")

In our data the only North row with an amount of 2000 or more is row 4 (the Backpack, 2800) and row 8 (the pens, 2400), so those two show Bonus.

Nested IF: more than two outcomes

When you have three or more outcomes, put one IF inside another. Grade a sale as High, Medium or Low.

=IF(G2>=2500, "High", IF(G2>=1500, "Medium", "Low"))

Excel checks the first test. If it is true you get “High”. If not, it goes to the second IF inside. Rows are checked from the top, so put the strictest condition first. Rows 4 and 7 (2800) show High. Rows 3, 5, 6, 8 and 10 (2000, 1800, 1950, 2400 and 2400) show Medium, and the other three show Low.

Nested IFs get hard to read after three levels. Newer Excel versions (2019 and 365) have IFS, which lists conditions in turn, and SWITCH for exact matches. A lookup table (lesson 7) is the cleanest choice when there are many bands.

=IFS(G2>=2500, "High", G2>=1500, "Medium", TRUE, "Low")

IFERROR: tidy up errors

A formula that hits an error shows ugly codes like #DIV/0!. IFERROR(value, value_if_error) replaces the error with something friendly.

=IFERROR(G2/D2, 0)
=IFERROR(VLOOKUP(B2, Prices, 2, FALSE), "Not found")

Use it with care. It can hide real mistakes. Prefer to handle expected cases (an empty denominator, a missing code) and let unexpected errors show, so that you notice them.

A realistic example: pass or fail

A training centre records marks out of 100. Pass is 40 or more in each of two papers. A formula in the result column:

=IF(AND(B2>=40, C2>=40), "Pass", "Fail")

The same idea works for attendance (IF(days>=75%...)), overdue invoices (IF(TODAY()>due, "Overdue", "OK")) and low stock (IF(stock<=reorder, "Order", "OK")).

Common mistakes

  • Forgetting quotes around text results. =IF(A1>5, Yes, No) gives #NAME?.
  • Using a comma where your region wants a semicolon.
  • Putting the conditions in the wrong order in a nested IF, so an earlier test catches everything.
  • Comparing text that has extra spaces or different spelling.
  • Counting brackets wrongly. Click in the formula and Excel highlights matching brackets.

Your task

  1. Add a Size column with IF (Large from 2000), using a labelled threshold cell.
  2. Add a Grade column with a nested IF or IFS for High, Medium and Low.
  3. Add a Bonus column that uses AND for North and 2000 or more.
  4. Add a column Qty per rupee = IFERROR(D2/E2, 0) and think about when this might fail.

How to check it worked

  • Size shows Large on five rows (3, 4, 7, 8 and 10, the amounts 2000, 2800, 2800, 2400 and 2400) and Small on the other five.
  • Bonus shows on rows 4 and 8 only.
  • You can explain why the order of conditions matters in a nested IF.

Frequently asked questions

How many IFs can I nest in Excel?

Modern Excel allows many levels, but more than three become hard to read. Use IFS, SWITCH or a lookup table when there are many outcomes.

Why does IFERROR sometimes cause problems?

It hides every error, including real mistakes. Use it for expected cases such as division by zero, and keep unexpected errors visible so you can fix them.