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
When they find an outlier, many students delete it straight away. Stop! An outlier means "check it", not "delete it". A big order during Diwali can be genuine. Verify with the source (the order system, the store manager), and write your decision (keep / correct / exclude) in a note column.
Ravindra Bagale's Tip – मराठी
Outlier सापडला की बरेच students लगेच delete करतात. थांबा! Outlier म्हणजे "check करा", "delete करा" नाही. दिवाळीला एखादा मोठा order खरंच असू शकतो. Source (order system, store manager) सोबत verify करा, आणि निर्णय (keep / correct / exclude) एका note column मध्ये लिहा.
Ravindra Bagale's Tip – हिंदी
Outlier मिलते ही बहुत से students उसे तुरंत delete कर देते हैं. रुको! Outlier का मतलब "check करो", "delete करो" नहीं. दिवाली पर कोई बड़ा order सच में हो सकता है. Source (order system, store manager) से verify करो, और फ़ैसला (keep / correct / exclude) एक note column में लिखो.
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.