Formula Errors

Excel Formula to Check if a Value is Within a Percentage Range (±0.6%)

Guest Writer Sep 27, 2026 869 views

Question details

The user needs an Excel formula to evaluate whether a percentage value in a specific column falls between -0.6% and 0.6%, returning 0 if it is within the range and 1 if it is outside.

How to Check if a Value is Within ±0.6% Using an Excel Formula

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Validating spreadsheet data within a specific tolerance or percentage variance.
Observed behavior: Needs to output 0 for values within the inclusive range [-0.6%, 0.6%] and 1 for values outside this range.

Before you start

Ensure the target cells (such as column L) are formatted as percentages or numbers, as formulas referencing percentages behave differently depending on the data type in the cell.

Solution 1Recommended

Use the IF and AND Functions to Check the Range

Combining the IF and AND functions is the most direct way to evaluate multiple conditions and return specific binary results based on the outcome.

The AND function checks if multiple conditions are simultaneously true. We nest it inside the IF function to return 0 when the conditions are met, and 1 when they are not.

Select the output cell

Click on the cell where you want the result to appear (e.g., E37).

Enter the IF and AND formula

Type =IF(AND(L37>=-0.6%,L37<=0.6%),0,1) into the formula bar and press Enter.

Apply the formula to other cells

Click the bottom-right corner of cell E37 and drag the fill handle down to E48 to apply the formula to the rest of the column.

Use the IF and AND Functions to Check the Range

Decimal Equivalents: You can also use decimals instead of percentages in the formula: =IF(AND(L37>=-0.006, L37<=0.006), 0, 1).

Solution 2

Use the ABS Function for Symmetrical Ranges

Since the variance range is perfectly symmetrical (±0.6%), utilizing the Absolute Value (ABS) function can simplify your logical formula.

Powerful Spreadsheet Tool

Easily Validate Data Ranges with WPS Spreadsheet

WPS Spreadsheet provides robust formula support, allowing you to validate data, run IF/AND conditions, and manage percentage calculations with ease.

1. Open your file: Launch WPS Office and open your spreadsheet document.
2. Type the formula: Select the target cell and enter =IF(AND(L37>=-0.6%,L37<=0.6%),0,1).
3. Drag to fill: Use the fill handle to drag and apply the formula to the entire column seamlessly.

Fully compatible with Microsoft Excel formulas (.xlsx formats).Easy-to-use interface for writing complex IF, AND, and ABS functions.Free, lightweight, and fast performance for large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula returning an error when I use the % sign?

If your region settings or cell formats do not support direct percentage input in formulas, try using the decimal equivalent instead. For 0.6%, use 0.006 in your formula.

Can I change the formula to return text instead of 0 and 1?

Yes. You can replace the 0 and 1 in the IF statement with text enclosed in quotation marks. For example: =IF(AND(L37>=-0.6%,L37<=0.6%),"Within Range","Out of Range").

How do I check if a value is strictly greater than -0.6% and strictly less than 0.6%?

To make the range exclusive, simply remove the equal signs from the logical operators in the formula: =IF(AND(L37>-0.6%,L37<0.6%),0,1).

Excel & Data Problems