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 |
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 | – |
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.