Conditional Operations with IF

Conditional operations on MO-210

The Calculate and transform data objective includes conditional operations using IF. At Excel Associate (MO-210) level, you write formulas that branch: if a test is true, show one result; otherwise show another. You are not building Expert dashboards with IFS, SWITCH, or deep nested trees—those belong more to MO-211 territory. Know IF thoroughly; nest only lightly when a project needs a third outcome.

Task language sounds like "In column E, display Pass if the score is 70 or higher; otherwise display Fail", "Show Bonus if sales exceed the quota in H1; otherwise show 0", or "Return Yes when Quantity is greater than ReorderLevel." Graders look for a correct IF structure in the formula bar, not only a coincidentally correct static label.

IF syntax

Full form:

=IF(logical_test, value_if_true, value_if_false)

Example:

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

If B2 is 85, the cell shows Pass. If B2 is 62, it shows Fail.

Comparison operators for logical tests

Text in tests and text results needs quotation marks: "Pass", "NY". Numbers do not: 70, 0. Cell references are bare: B2, $H$1.

Worked example: pass / fail

After fill:

• C2 → Pass
• C3 → Fail
• C4 → Pass (70 meets >=)

Using > instead of >= would incorrectly fail a score of exactly 70—read the project wording ("70 or higher" vs "above 70").

Worked example: numeric outputs and absolute thresholds

Quota lives in H1 as 5000. Column B has sales; column C should show a $200 bonus when sales meet or exceed the quota, otherwise 0.

In C2:

=IF(B2>=$H$1,200,0)

Fill down. $H$1 stays locked so every row compares to the same quota. If you wrote H1 without dollars and filled down, lower rows would compare to H2, H3, and so on—classic reference mistake mixed into an IF task.

You can also return a calculated value:

=IF(B2>=$H$1,B2*0.05,0)

True branch: five percent of that row's sales. False branch: zero.

Worked example: text status from inventory

Reorder when on-hand quantity in B2 is less than or equal to reorder level in C2:

=IF(B2<=C2,"Reorder","OK")

Both sides of the test can be cell references. No absolute locks needed if each row has its own reorder level and you fill down relatively.

Filling IF down a column

1. Write the IF correctly in the first data row.
2. Decide which references must be absolute (shared rate, quota, passing score in one cell).
3. Fill or double-click the fill handle.
4. Spot-check a TRUE row and a FALSE row, and open Show Formulas to confirm $ signs.

IF does not auto-expand like a dynamic array; it is a normal scalar formula per cell—exactly what Associate projects expect for status columns.

Light nesting (Associate only)

Sometimes a project needs three labels, such as High / Medium / Low. A nested IF places another IF in the false (or true) branch:

=IF(B2>=90,"High",IF(B2>=70,"Medium","Low"))

Evaluation order:

1. If score ≥ 90 → High
2. Else if score ≥ 70 → Medium
3. Else → Low

Keep nesting shallow (two IF levels is enough for Associate). Deep nests are hard to grade and rare on MO-210.

Brief contrast: IFS (not the MO-210 focus)

Microsoft 365 also offers IFS, which lists condition/result pairs:

=IFS(B2>=90,"High",B2>=70,"Medium",TRUE,"Low")

Know that IFS exists so you are not confused if you see it in help docs—but for Exam MO-210, prefer standard IF (and light nesting) unless a task explicitly names IFS. Do not rewrite every IF as IFS "to be modern"; the Associate objective calls out IF.

Combining IF with aggregates

Flag values above the column average:

=IF(B2>AVERAGE($B$2:$B$20),"Above avg","OK")

Or count-related logic using a helper column of IF results, then COUNTA on "Yes" labels—though many projects only ask for the IF column itself.

Boolean tests can compare to COUNT results too:

=IF(COUNT(D2:D20)>=10,"Enough scores","Need more")

That single-cell summary IF sits outside the data block and still uses Domain 4 counting skills.

Quotes, blanks, and data types

• =IF(A2="","Missing","Present") tests for a blank cell.
• Returning "" (empty text) hides a false branch: =IF(B2>=70,"Pass","").
• Do not write =IF(B2>=70,Pass,Fail) without quotes—Excel treats unquoted Pass/Fail as names and often returns #NAME?.
• Mixing numbers and text in outputs is fine: true → "Qualified", false → 0.

Common MO-210 traps

• Forgetting quotes around text results → #NAME?.
• Using = when the English says "at least" (>=) or "more than" (>).
• Leaving a quota cell relative when filling IF down.
• Nesting five levels or switching to IFS when the project asked for IF.
• Typing the true and false values in the wrong order (fail-first logic).
• Comparing text numbers incorrectly when a cell is text-formatted—ensure scores are numeric if you use >=70.

Practice sequence

1. Build Pass/Fail with >=70; test scores 69, 70, and 95.
2. Add a quota in H1; write bonus IF with $H$1; fill and break it once on purpose without $ to see the failure mode.
3. Write a two-level nested IF for High/Medium/Low and rewrite the same logic mentally as IFS—then stick with IF for exam practice.
4. Combine B2>AVERAGE($B$2:$B$15) inside IF and fill a flag column.

Solid IF skills—tests, quotes, fill locks, and light nesting—complete the conditional half of Calculate and transform data for MO-210.