Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

5.16 Error Values in Data

By Last updated:

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

  1. Find all errors: Home › Editing › Find & Select › Go To Special › Formulas › tick only Errors (or Constants › Errors for pasted error text).
  2. Count them: =SUMPRODUCT(--ISERROR(D2:D100)).
  3. Fix the cause (Module 3.17) – e.g. =IF(B3=0,"–",C3/B3) for division, =IFNA(XLOOKUP(…),"Missing in master") for lookups.
  4. To total a column that contains errors: =AGGREGATE(9,6,D2:D100) (9 = SUM, 6 = ignore errors) – see Module 6.

Ravindra Bagale's Tip

Report madhe #N/A kiwa #DIV/0! disla ki manager la vishwas rahat nahi – pan khup students te IFERROR ne 0 karun lapavtat, aani average chukto. Error la 0 nako, "–" kiwa blank dakhva, aani khare missing records vegle list kara. AGGREGATE ne totals error-proof hotat.

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.