Function Problems

Excel Formula to Return Pass or Fail for Tolerance Limits

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs an Excel formula to evaluate whether multiple sample values fall within a specific target value plus or minus a tolerance limit, returning 'Pass' or 'Fail'.

Product: Excel
Device & OS: not provided
Scenario: Performing quality control or data validation where multiple sample test values must be checked against acceptable upper and lower limits.
Observed behavior: A reliable formula is required to output 'Fail' if any sample value in a designated range falls outside the permitted target and tolerance bounds, and 'Pass' only if all values are strictly within limits.

Before you start

Verify that your data is organized into clearly defined cells for the target value, the acceptable tolerance limit, and the continuous range of sample values you want to test.

Solution 1Recommended

Use IF and COUNTIFS Functions to Evaluate Tolerance Limits

Combine the IF and COUNTIFS functions to count the number of values outside the acceptable range. If the count is greater than zero, the formula returns Fail; otherwise, it returns Pass.

This formula uses COUNTIFS to calculate how many cells in your sample range fall below the lower limit (Target minus Tolerance) or above the upper limit (Target plus Tolerance).

By nesting these counts inside an IF function, the spreadsheet outputs 'Fail' if any out-of-bounds values are detected, and 'Pass' if the count of invalid items is exactly zero.

Select the target output cell

Click on the cell where you want the Pass or Fail result to appear (for example, L72).

Identify reference cells

Note the specific cell containing your target value (e.g., E72), your tolerance limit (e.g., F72), and the range of your sample values (e.g., G72:K72).

Enter the formula

Type the formula: =IF(COUNTIFS(G72:K72,"<"&E72-F72)+COUNTIFS(G72:K72,">"&E72+F72),"Fail","Pass") and press Enter.

Verify the result

Change one of the sample values to fall outside the target and tolerance range to ensure the formula successfully updates from Pass to Fail.

Using Absolute References: If you plan to drag this formula down multiple rows to test different sample sets, remember to lock your target and tolerance cells using absolute references (e.g., $E$72 and $F$72) if those limits apply to all rows.

WPS Spreadsheet Solution

Easily Evaluate Pass/Fail Data with WPS Spreadsheet

You can seamlessly apply complex logical formulas like IF and COUNTIFS to evaluate data tolerance limits using WPS Spreadsheet. It offers a smooth data validation experience and full compatibility with standard spreadsheet functions.

1. Open WPS Spreadsheet: Launch WPS Office, open your spreadsheet, and ensure your target, tolerance, and sample data are organized.
2. Select the Result Cell: Click on the cell where you want the Pass/Fail validation result to be displayed.
3. Input the Logic Formula: Type =IF(COUNTIFS(data_range,"<"&target-tolerance)+COUNTIFS(data_range,">"&target+tolerance),"Fail","Pass") and press Enter.
4. Apply to Multiple Rows: Click the bottom-right corner of the result cell and drag the fill handle down to apply the evaluation formula to additional data rows.

Fully compatible with Microsoft Excel formulas, functions, and formats (.xlsx).Built-in intuitive function wizard to help build and troubleshoot IF and COUNTIFS formulas.Lightweight software that processes large arrays of quality control data quickly.Free to download and simple to navigate with a familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

How do I highlight the Pass and Fail results in different colors?

You can achieve this using Conditional Formatting. Select the result cells, go to the Home tab, and click 'Conditional Formatting' > 'Highlight Cells Rules' > 'Text that Contains'. Create one rule for 'Pass' with green text, and another for 'Fail' with red text.

Can I use hardcoded numbers instead of cell references for the tolerance limits?

Yes. If your target is 100 and your tolerance is 5, you can type the actual values into the formula like this: =IF(COUNTIFS(G72:K72,"<95")+COUNTIFS(G72:K72,">105"),"Fail","Pass"). However, using cell references is recommended so you can update limits without editing the formula directly.

Why is my formula returning Pass when a cell is completely blank?

Depending on the evaluation context, blank cells might be evaluated as zeroes. To prevent blank cells from affecting your results, you can add an additional criteria range and criteria to your COUNTIFS function to only count cells that are not empty, using the "<>" operator.

Excel & Data Problems