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 |
About this data: Teaching sample for a fictional shop “CityBasket Retail”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on outliers.
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 |
About this data: Demo table only — not real customer or company data. Built to practise outliers.
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.
Loading practice tasks...
- Average (Easy): Find the average of C2:C11.
- Median (Easy): Find the median of C2:C11.
- Biggest value (Easy): Find the biggest value in C2:C11.
- Smallest value (Easy): Find the smallest value in C2:C11.
- First quartile (Q1) (Medium): Find Q1 of C2:C11.
- Third quartile (Q3) (Medium): Find Q3 of C2:C11.
- IQR (Medium): Find the IQR of C2:C11.
- Upper fence (Medium): Find the upper fence.
- Is ATM-07 an outlier? (Hard): Show "Outlier" if C8 is above the upper fence or below the lower fence, else "OK".
- Is ATM-02 an outlier? (Hard): Same test for C3.
- Z-score of ATM-09 (Hard): Find the z-score of C10.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
