IF, AND & OR
Turn a written policy into testable, readable decision rules.
By the end, you’ll be able to:
- Distinguish all required conditions from any acceptable condition
- Nest a decision in a defined order
- Test boundaries and conflicting cases
Write the rule in ordinary words first
An order is ready for release when it is paid and stock is available. Both tests are necessary. A priority order qualifies when its value is at least 150 or a separate priority flag is set; either test can be enough.
AND and OR return TRUE or FALSE. IF turns that result into a label, amount or further calculation. Avoid hiding the policy inside a long formula before you have agreed what should happen.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Value | Paid | Stock available |
| 2 | S201 | 120 | Yes | Yes |
| 3 | S202 | 80 | Yes | No |
| 4 | S203 | 150 | No | Yes |
Build a logical test from smaller tests
=AND(logical1, [logical2], ...)=OR(logical1, [logical2], ...)=IF(logical_test, value_if_true, [value_if_false])Each logical argument is a condition. AND returns TRUE when every supplied condition is true; OR returns TRUE when at least one is true. IF uses its logical_test to choose the true or false result. Include both results explicitly for a clear policy.
Use comparisons such as C2="Yes", not a bare text label as a logical test.
Worked example: release only a ready order
=IF(AND(C2="Yes",D2="Yes"),"Release","Hold") returns Release for S201. It returns Hold for S202 because stock is unavailable and for S203 because payment is missing.
Changing AND to OR would release both unready orders: each passes one condition. Check all four combinations of two true/false inputs. The wrong connector can produce plausible labels while implementing the wrong policy.
Make precedence explicit in nested IF
Suppose unpaid orders must be Hold; among paid orders, values of at least 100 receive Priority and smaller values receive Standard. Use =IF(C2="Yes",IF(B2>=100,"Priority","Standard"),"Hold").
S201 is Priority, S202 is Standard and S203 remains Hold despite its high value. The outer condition decides whether to assess priority. Test a paid order at exactly 100 and an unpaid high-value order to check the rule’s precedence.
Keep complexity visible
For a larger policy, use helper columns with descriptive headers such as Paid?, Stock ready? and Release status. They make intermediate results easier to inspect and explain. More nesting is not automatically better.
Ordinary text comparisons ignore case, but spaces and inconsistent labels remain problems. Use validation for controlled statuses. These functions work across current web Excel and established desktop editions. The checker tests both sides of each policy rather than accepting a single displayed answer.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
The practice checker accepts any supported formula that solves the task, including whole-column ranges where supported, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
AND requires every condition; OR accepts any condition. Write policy precedence clearly and test one-failing-condition cases.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Technical review: 2026-10-16. Page updated: 2026-10-19. How SheetSprout develops and checks lessons.