The Excel IF function: a guide for skills tests
IF lets a formula make a decision. It's one of the most common step-ups from basic formulas on an Excel test, and it's the foundation for several others, so it's worth getting comfortable with.
How IF works
IF has three parts: a test, what to return if the test is true, and what to return if it's false.
For example, =IF(B2>=70, "Pass", "Fail") reads: if B2 is 70 or more, show Pass. Otherwise, show Fail.
Text goes inside straight quotation marks, and numbers don't. You can also return another calculation, as in =IF(B2>1000, B2*0.1, 0), which gives a 10% bonus on sales over 1,000 and nothing otherwise.
More than two outcomes: nested IF
To choose between more than two results, put another IF in the false part. For example: =IF(B2>=90, "A", IF(B2>=80, "B", "C")).
Excel stops at the first test that's true, so order matters. If you checked for 80 first, a score of 95 would get a B. Check the highest value first, and make sure every opening bracket has a closing one.
More than one condition: AND and OR
- AND needs every condition to be true: =IF(AND(B2>=70, C2="Yes"), "Hire", "No")
- OR needs at least one to be true: =IF(OR(B2="Boston", B2="Denver"), "East or West", "Other")
Common mistakes
- Forgetting quotes around text, which often gives a #NAME? error
- Using curly quotes pasted from another program instead of straight ones
- Getting boundaries wrong: > leaves out the number itself, and >= includes it
- Typing a fixed number where the task expects a reference to a cell
More guides
DeskReady is an independent practice tool, not affiliated with any software vendor or employer. Tests vary, so use this as a general guide.