A value on the fence is flagged
Use less-than and greater-than, not their inclusive versions. The on-fences and outside-fences datasets distinguish the rules.
Flag observations outside the IQR fences
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill G5:G9 separately: Q1 and Q3 from QUARTILE.INC, lower fence Q1-1.5*(Q3-Q1), upper fence Q3+1.5*(Q3-Q1), then the number of numeric B2:B11 values strictly below or above those fences. Values on a fence remain inside. Ignore referenced text, blanks and booleans. With no numeric observations every output returns No observations. Keep the original records; a flag only requests investigation.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5, G6, G7, G8, G9. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Calculate inclusive quartiles and two 1.5-IQR fences, then count measured values strictly outside them.
THE QUICK ANSWER01
Calculate inclusive quartiles and two 1.5-IQR fences, then count measured values strictly outside them.
G5
=IF(COUNT(B2:B11)=0,"No observations",QUARTILE.INC(B2:B11,1))G6
=IF(COUNT(B2:B11)=0,"No observations",QUARTILE.INC(B2:B11,3))G7
=IF(ISNUMBER(G5),G5-1.5*(G6-G5),G5)G8
=IF(ISNUMBER(G6),G6+1.5*(G6-G5),G6)G9
=IF(ISNUMBER(G7),SUMPRODUCT(ISNUMBER(B2:B11)*((B2:B11<G7)+(B2:B11>G8))),G7)For the example data
G5 3.25G6 6.75G7 -2G8 12G9 2
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
A reproducible convention
Q1 and Q3 use the inclusive Excel method in this exercise. The IQR is Q3 minus Q1; multiplying that width by 1.5 defines the inner fences. The reported count uses strict comparisons against those fences, including both signed tails.
Zero IQR does not mean no candidates
When Q1 and Q3 both equal 5, both fences equal 5. Eight measured fives plus a measured fifty therefore flag one value under the declared rule. A singleton has matching quartiles and no outside values, but is too small to establish a dependable process pattern.
Investigate the observation
A flagged value can be valid, a recording error or a different process. The rule neither proves an error nor authorizes removing the row. Use domain context before changing the original data.
Read the full step-by-step walkthrough
- Declare the quartile convention
Enter G5 and G6 separately. This task uses QUARTILE.INC: Q1 is the 25th percentile and Q3 the 75th, both with inclusive interpolation.
- Calculate the other quartile
The original Q1 is 3.25 and Q3 is 6.75. Other quartile conventions can differ, especially for small samples.
- Build the lower fence from the IQR
Enter G7. The original IQR is 6.75-3.25 = 3.5, so the lower fence is 3.25-1.5*3.5 = -2.
- Build the upper fence
Enter G8. The upper fence is 6.75+1.5*3.5 = 12. The two fences use the same IQR.
- Count strictly outside numeric observations
Enter G9. In the original source, -10 and 30 lie outside the fences, so the count is two. Text numbers and values exactly on a fence do not count.
- Keep a review flag separate from deletion
This is a descriptive screening rule. Retain the measurements, investigate their context and document any later decision about them.
Things to watch
The upper fence starts from Q1
The upper fence begins at Q3 and adds 1.5 times the IQR.
Zero IQR forces a zero count
Equal quartiles can coexist with a distant observation. Compare every numeric value with the actual fences.
About this example
Practice calculates in the local browser spreadsheet engine. Enter each report formula separately; filling down does not create the other metrics. Starter workbooks retain the original ten-row sample; current-data downloads retain current values and formulas. Native Excel and Google Sheets recalculation/edit/save remain unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.