Finding outliers with QUARTILE and the IQR rule
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which formula finds Q1 for the data in B2:B100?
2. Which formula finds Q3 for the data in B2:B100?
3. How do you work out the IQR if Q1 is in E1 and Q3 is in E2?
4. Which formula gives the upper fence if Q3 is in E2 and the IQR is in E3?
5. Which formula gives the lower fence if Q1 is in E1 and the IQR is in E3?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=QUARTILE.INC(B2:B100,1) | A=IF(OR(B2<F2,B2>G2),"Outlier","OK") | |
| 2=QUARTILE.INC(B2:B100,3)-QUARTILE.INC(B2:B100,1) | BUsing 2 gives the median (Q2), not Q1. Use 1 for Q1 and 3 for Q3. | |
| 3The lower fence is 1.5 times the IQR below Q1. Any value under it is an outlier. | CFinds Q1, the value 25% of the way up the data, which is the first step in the IQR rule | |
| 4Flag a value as an outlier if it is below the lower fence or above the upper fence | DCalculates the IQR by subtracting Q1 from Q3 | |
| 5=QUARTILE.INC(B2:B100,2) | EThe upper fence should be Q3 + 1.5*IQR, but this adds 1.5 times Q1 instead of the IQR. | |
| 6=D3+1.5*D2 | F=D2-1.5*D4 |
C
Definitions
=QUARTILE.INC(B2:B100,1)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=____________(B2:B100,1)
2.
=QUARTILE.INC(B2:B100,_)
3.
=E2_D2
4.
=D2-___*F2
5.
=IF(__(B2<G2,B2>H2),"Outlier","OK")
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
D1: =QUARTILE.INC(B2:B100,1)
B
D2: =QUARTILE.INC(B2:B100,2)
C
D3: =D2-D1
Question 2
A
D1: =QUARTILE.INC(B2:B100)
B
D2: =QUARTILE.INC(B2:B100,3)
C
D3: =D2-D1
Question 3
A
D1: =QUARTILE.INC(B2:B100,1)
B
D2: =QUARTILE.INC(B2:B100,3)
C
D3: =D1-D2
D
D4: =D1-1.5*D3
Question 4
A
D1: =QUARTILE.INC(B2:B100,1)
B
D2: =QUARTILE.INC(B2:B100,3)
C
D3: =D2-D1
D
D4: =D1-1.5*D2
E
D5: =D2+1.5*D3
Question 5
A
D1: =QUARTILE.INC(B2:B100,1)
B
D2: =QUARTILE.INC(A2:A100,3)
C
D3: =D2-D1
D
D4: =D2+1.5*D3
Answer Key
Finding outliers with QUARTILE and the IQR rule
Section A: Quick Questions
Q1
A — =QUARTILE.INC(B2:B100,1)
Q2
A — =QUARTILE.INC(B2:B100,3)
Q3
A — =E2-E1
Q4
B — =E2+1.5*E3
Q5
B — =E1-1.5*E3
Section B: Match the Pairs
Q1
=QUARTILE.INC(B2:B100,1) → Finds Q1, the value 25% of the way up the data, which is the first step in the IQR rule
Q2
=QUARTILE.INC(B2:B100,3)-QUARTILE.INC(B2:B100,1) → Calculates the IQR by subtracting Q1 from Q3
Q3
The lower fence is 1.5 times the IQR below Q1. Any value under it is an outlier. → =D2-1.5*D4
Q4
Flag a value as an outlier if it is below the lower fence or above the upper fence → =IF(OR(B2<F2,B2>G2),"Outlier","OK")
Q5
=QUARTILE.INC(B2:B100,2) → Using 2 gives the median (Q2), not Q1. Use 1 for Q1 and 3 for Q3.
Q6
=D3+1.5*D2 → The upper fence should be Q3 + 1.5*IQR, but this adds 1.5 times Q1 instead of the IQR.
Section C: Definitions
Q1
=QUARTILE.INC(B2:B100,1) — Finds Q1, the value 25% of the way up the data. Use 3 for Q3. IQR = Q3 - Q1. A value more than 1.5 times the IQR below Q1 or above Q3 is flagged as an outlier.Section D: Fill in the Blanks
Q1
=QUARTILE.INC(B2:B100,1)Q2
=QUARTILE.INC(B2:B100,3)Q3
=E2-D2Q4
=D2-1.5*F2Q5
=IF(OR(B2<G2,B2>H2),"Outlier","OK")Section E: Spot the Error
Q1
B — Quart 2 returns the median, not Q3. Q3 needs quart 3, so the line should be =QUARTILE.INC(B2:B100,3).
Q2
A — QUARTILE.INC needs a second argument saying which quartile to find. For Q1 it should be =QUARTILE.INC(B2:B100,1).
Q3
C — The IQR is Q3 - Q1. Here Q1 - Q3 would give a negative answer. It should be =D2-D1.
Q4
D — The lower fence is Q1 minus 1.5 times the IQR. This line multiplies Q3 (D2) instead of the IQR (D3). It should be =D1-1.5*D3.
Q5
B — The data being tested is in column B, but this line reads column A. It should be =QUARTILE.INC(B2:B100,3).