Exceljet

Quick, clean, and to the point

Filter values within tolerance

Excel formula: Filter values within tolerance
Summary 

To extract and display values within a given tolerance from a larger set of data, you can use the FILTER function together with the ABS function. In the example shown, the formula in E9 is:

=FILTER(data,(data[Group]=F4)*(ABS(data[Value]-F5)<=F6))

where "data" is an Excel Table in the range B5:C16. The result is a list of the values in Group A that are within +/- 0.05 of 1.2.

Explanation 

In this example, the goal is to list values in a given group that are within a given tolerance. The data comes from an Excel Table called "data" in the range B5:C16. The solution is built on the FILTER function which can be used to extract and list data that meets multiple criteria. The variable inputs to the formula come from the range F4:F5. When inputs change, the results update immediately.

Video: Basic FILTER function example

Filter by Group

In order to extract records where the group is "A", we can use FILTER like this:

=FILTER(data,data[Group]="A") // group A only

Here, the array argument is the entire table, since we want both columns in the output. The include argument is supplied as a simple logical expression that returns TRUE or FALSE for each value in the Group column:

data[Group]="A" // returns TRUE or FALSE

Because we want the group to be a variable input, we need to replace the hardcoded text string "A" with a reference to cell F4, which allows the user to change the group as desired:

=FILTER(data,data[Group]=F4)

Now when a user types "B" into cell F4, FILTER will extract all values from group B.

Values within tolerance

The next task in the formula is to test for values within a given tolerance, where the target value comes from cell F5, and the acceptable tolerance is defined in cell F6. The generic logical expression for this test looks like this:

ABS(value-target)<=tolerance)

The ABS function is used to convert negative differences to positive values. See this formula for a more detailed explanation. Mapping the cell references in the example to the generic formula, we get this logical expression:

ABS(data[Value]-F5)<=F6)

This expression will return TRUE or FALSE for each number in the Value column. To extract all values within tolerance, ignoring group, we can use the expression as the include argument in FILTER:

=FILTER(data,ABS(data[Value]-F5)<=F6)

FILTER will ignore group and return all values within tolerance.

Combining expressions

Now we need to combine both logical conditions above into a single formula. For this, we use Boolean logic. Because we want to join the two expressions with AND (i.e. we want to enforce both conditions) we use the multiplication operator between the expressions like this:

=(data[Group]=F4)*(ABS(data[Value]-F5)<=F6)

Each expression generates its own array of TRUE and FALSE values, and the multiplication operation automatically coerces the TRUE and FALSE values to 1s and 0s. This standalone formula will return 1 for values that meet both conditions, and 0 for other values.

Video: Boolean algebra in Excel

Finally, we need to place this expression into the FILTER function as the include argument:

=FILTER(data,(data[Group]=F4)*(ABS(data[Value]-F5)<=F6))

This is the final formula. With "A" in F4, 1.2 in F5, and 0.05 in F6, the FILTER function returns rows in the table where values in Group A are within +/- 0.05 of 1.2. These results are returned to cell E9 and spill onto the worksheet. If values in F4:F6 are changed, results are immediately updated.

Dynamic Array Formulas are only available in Excel 365 and Excel 2021.
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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.