In this lesson
Trace a ratio to its inputs, correct a text amount and write a clear rule for an unplanned row.
Example
Completion rates when one team has no plan
On Audit, enter Team, Completed and Planned in A1:C4: Support 8 10, Sales 0 0 and Operations 6 8. Calculate completion for each team. Sales has no plan, so its ratio needs a label rather than a misleading percentage.
How it works
The error tells you where to start looking. In this ratio, #DIV/0! means the denominator is zero or blank. Inspect Planned before deciding how that row should be reported.
IFERROR catches many kinds of failure, including broken references. Use a specific zero-plan condition here so another error remains visible instead of being labeled Not planned.
Steps
Type Completion in D1, enter the formula below in D2 and fill down to D4. Check Support 0.8, Sales #DIV/0! and Operations 0.75.
=B2/C2English function names. Formula settings →
Select the Sales result and inspect its Completed and Planned inputs. On desktop, try Trace Precedents or Evaluate Formula where available; in web Excel, read the references in the formula bar.
Replace D2 with the zero-plan formula below and fill down. Sales should show Not planned. This label describes the missing denominator rather than a performance result.
=IF(C2=0,"Not planned",B2/C2)English function names. Formula settings →
Type Imported in F1 and an apostrophe followed by 120 in F2. In nearby cells compare =ISNUMBER(F2) with =VALUE(F2) to see the stored type and its numeric conversion.
Use Formulas > Show Formulas where available. Check that every Completion formula uses the matching row’s inputs, then turn Show Formulas off.
On ReviewLog, note the failing cell, its original formula, the cause, the correction and the expected result. Store the original formula as text so it remains readable without leaving a hidden broken calculation.
Try it yourself
- Add Facilities with Completed 5 and Planned 10 and extend the ratio formula. Add another team with Completed 0 and Planned 0, and confirm the same documented exception applies.
- Deliberately delete a referenced cell on a disposable copy and observe the reference error. Explain why changing every error to Not planned would misclassify that structural problem.
Show expected result
- Support displays 80 percent, Operations 75 percent and Facilities 50 percent after percentage formatting; each zero-plan row says Not planned.
- The text import fails ISNUMBER until converted deliberately. A broken reference remains an investigation item rather than being mislabeled as a zero plan.
If something goes wrong
- The cell shows the formula instead of a result.
- Check for Text format or a leading apostrophe. Change the format to General and re-enter the formula. If formulas across the sheet are visible, turn Show Formulas off.
- The result stays the same after an input changes.
- Check calculation mode and recalculate. Then inspect the formula’s input addresses to make sure it refers to the cell you edited.
Quick check
Try these questions after the exercise. Check the result in your app before marking the lesson complete.
Choose an answer for all 3 questions to check your understanding.
Optional: try it with Copilot
If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.
Explain these formula errors separately. Suggest a rule for Planned = 0, and point out reference errors that the rule should not hide.
More from Microsoft
These Microsoft guides cover the tools used in this lesson.
Loading your progress…