Exceljet

Quick, clean, and to the point

Count values out of tolerance

Excel formula: Count values out of tolerance
Generic formula 
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Explanation 

To count values that are out of tolerance in a set of data, you can use a formula based on the SUMPRODUCT and ABS functions. In the example shown, the formula in F6 is:

=SUMPRODUCT(--(ABS(data-target)>tolerance))

where "data" is the named range B5:B14, "target" is the named range F4, and "tolerance" is the named range F5.

How this formula works

This formula counts how many values are not in range of a fixed tolerance. The variation of each value is calculated with this:

ABS(data-target)

Because the named range "data" contains 10 values, subtracting the target value in F4 will created an array with 10 results:

{0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006}

The ABS function changes any negative values to positive:

{0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006}

This array is compared to the fixed tolerance in F5:

ABS(data-target)>tolerance

The result is an array or TRUE FALSE values, and the double negative changes these to ones and zeros. Inside SUMPRODUCT, the final array looks like this:

{0;0;1;0;1;0;0;1;0;1}

where zeros represent values within tolerance, and 1s represent values out of tolerance. SUMPRODUCT then sums the items in the array, and returns a final result, 4.

All values within tolerance

To return "Yes" if all values in a data range are within a given tolerance, and "No" if not, you can adapt the formula like this:

=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")

If SUMPRODUCT returns any number greater than zero, IF will evaluate the logical test as TRUE. A zero result will be evaluated as FALSE.

Highlight values out of tolerance

You can highlight values out of tolerance with a conditional formatting rule based on a formula like this:

=ABS(B5-target)>tolerance

Highlight values out of tolerance

This page lists more examples of conditional formatting with formulas.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.