Part II: Working with Formulas and Functions
FIGURE 16.16
Leveraged interquartile ranges expand the fences outward.
To determine the upper fence, we multiply the fence factor by the interquartile range and
add the result to the 75th percentile. The same result is subtracted from the 25th percentile
to establish the lower fence.
You may find that fence factor of 1.5 excludes values that you consider outliers or includes values that you consider
normal. There’s nothing magic about 1.5. Simply adjust the factor up or down if it doesn’t fit your data.
With our fences established, we use a nested IF formula to determine whether each value
is greater than the upper fence or less than the lower fence. The text “High” or “Low” is
returned by the nested IF formula for the outliers, and an empty string ("") is returned for
those that are inside the fences.
