where “data” is the named range B5:B14, “target” is the named range F4, and “tolerance” is the named range F5. Because the named range “data” contains 10 values, subtracting the target value in F4 will created an array with 10 results: The ABS function changes any negative values to positive: This array is compared to the fixed tolerance in F5: 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: 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 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:
This page lists more examples of conditional formatting with formulas.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
