5.16 Error Values in Data
Before
| Store ID | Orders | Sales | AOV |
|---|---|---|---|
| BLK-PUN-01 | 412 | 1,31,840 | 320 |
| BLK-NSK-01 | 0 | 0 | #DIV/0! |
| BLK-XXX-99 | 25 | #N/A | #N/A |
About this data: Teaching sample for a fictional shop “BrightLedger Finance”. Figures and names (e.g. Aamir Khan) are made up for this lesson on error values in data.
After
| Store ID | Orders | Sales | AOV |
|---|---|---|---|
| BLK-PUN-01 | 412 | 1,31,840 | 320 |
| BLK-NSK-01 | 0 | 0 | – |
| BLK-XXX-99 | 25 | Missing in master | – |
About this data: Demo table only — not real customer or company data. Built to practise error values in data.
Steps in Excel
- Find all errors: Home › Editing › Find & Select › Go To Special › Formulas › tick only Errors (or Constants › Errors for pasted error text).
- Count them:
=SUMPRODUCT(--ISERROR(D2:D100)). - Fix the cause (Module 3.17) – e.g.
=IF(B3=0,"–",C3/B3)for division,=IFNA(XLOOKUP(…),"Missing in master")for lookups. - To total a column that contains errors:
=AGGREGATE(9,6,D2:D100)(9 = SUM, 6 = ignore errors) – see Module 6.
Ravindra Bagale's Tip
When a manager sees #N/A or #DIV/0! in a report, they stop trusting it – but many students hide these by turning them into 0 with IFERROR, and then the average is wrong. Don't show an error as 0; show "–" or a blank, and list the truly missing records separately. AGGREGATE makes totals error-proof.
Ravindra Bagale's Tip – मराठी
Report मध्ये #N/A किंवा #DIV/0! दिसला की manager चा विश्वास राहत नाही – पण बरेच students ते IFERROR ने 0 करून लपवतात, आणि average चुकतो. Error ला 0 नको, "–" किंवा blank दाखवा, आणि खरे missing records वेगळे list करा. AGGREGATE ने totals error-proof होतात.
Ravindra Bagale's Tip – हिंदी
Report में #N/A या #DIV/0! दिखे तो manager का भरोसा उठ जाता है – पर बहुत से students उन्हें IFERROR से 0 बनाकर छुपा देते हैं, और average गलत हो जाता है. Error को 0 मत दिखाओ, "–" या blank दिखाओ, और असली missing records की अलग list बनाओ. AGGREGATE से totals error-proof हो जाते हैं.
Practice task
In a sheet with 5 kinds of errors, select them with Go To Special, count them, fix each cause, and total the column with AGGREGATE before and after fixing.
Loading practice tasks...
- Safe division (Easy): Divide C3 by D3. If there is an error, show 0.
- Normal division (Easy): Divide C2 by D2. If error, show 0.
- Find a manager (Easy): Find the manager of A3 from Master.
- Safe division with a note (Easy): Divide C3 by D3. If error, show "No visitors".
- Missing manager (Medium): Find the manager of A5. If not found, show "Not in Master".
- Is it a number? (Medium): Is C5 a number? (TRUE or FALSE)
- Sum that skips text (Medium): Total sales in C2:C7.
- Count good numbers (Medium): How many numbers are in E2:E7?
- Count error text (Hard): How many cells in E2:E7 start with "#"?
- Average of good values (Hard): Average of the numbers in E2:E7.
- Recalculate safely (Hard): Total of C2:C7 ÷ D2:D7 for each row, where errors count as 0.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
