Chapter 16: Using Formulas for Statistical Analysis
16
Fence Factor: 1.5
Upper Fence: =G4+(G6*G8)
Lower Fence: =G5-(G6*G8)Outliers: =IF(C3<$G$10,"Low",IF(C3>$G$9,"High",""))FIGURE 16.15
Identifying outliers using a leveraged interquartile range
The QUARTILE.EXC function is used to determine the 75th percentile and 25th percentile
using arguments of 3 and 1, respectively. The interquartile range is the difference between
these two.
In a nonleveraged interquartile range, you would simply subtract the interquartile range
from the 25th percentile to get a lower fence and add it to the 75th percentile to get an
upper fence. This method can result in too many outliers. By multiplying the interquartile
range by a factor (1.5 in this example), we expand the fences to isolate the truly extreme
values. Figure 16.16 shows the same data sorted by defects and the demarcation lines of the
quartiles, interquartile range, and the upper and lower fences.