5.15 Outliers
An outlier (बाकीपेक्षा खूप वेगळे, असामान्य मूल्य) is a value far away from the rest – a typo (₹12,990 instead of ₹129.90) or a genuine bulk order.
Before
| Order ID | Amount | Flag |
|---|---|---|
| BLK-3401 | 120 | |
| BLK-3402 | 95 | |
| BLK-3403 | 140 | |
| BLK-3404 | 12,990 | |
| BLK-3405 | 110 | |
| BLK-3406 | 0 |
After
| Order ID | Amount | Flag |
|---|---|---|
| BLK-3401 | 120 | OK |
| BLK-3402 | 95 | OK |
| BLK-3403 | 140 | OK |
| BLK-3404 | 12,990 | Outlier – verify |
| BLK-3405 | 110 | OK |
| BLK-3406 | 0 | Outlier – verify |
Steps in Excel – IQR (inter-quartile range) method
- Q1:
=QUARTILE.INC($B$2:$B$7,1), Q3:=QUARTILE.INC($B$2:$B$7,3)(put in E1, E2). - IQR:
=E2-E1. Lower fence=E1-1.5*E3, upper fence=E2+1.5*E3. - Flag:
=IF(OR(B2<$E$4,B2>$E$5),"Outlier – verify","OK"). - Visual check: Insert › Charts › Insert Statistic Chart › Box and Whisker (Excel 2016+) shows outliers as dots.
- Alternative z-score:
=STANDARDIZE(B2,AVERAGE($B$2:$B$7),STDEV.S($B$2:$B$7)); |z| > 3 is unusual (works best with many rows).
Ravindra Bagale's Tip
Outlier sapadla ki khup students lagech delete kartat. Thamba! Outlier mhanje "check kara", "delete kara" nahi. Diwali la ekhada motha order kharach asu shakto. Source (order system, store manager) sobat verify kara, aani nirnay (keep / correct / exclude) ek note column madhe liha.
Practice task
For 30 order amounts calculate the IQR fences and flag outliers. Draw a box-and-whisker chart and compare the dots with your flags.