'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Excel Formula to Check if a Number is Within 20 Percent
Question details
The user needs an Excel formula to determine if a value in one column falls within a 20% range (above or below) of a corresponding value in another column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two numerical columns to verify if their difference falls within a specified percentage tolerance.
- Observed behavior
- The user wants to automatically output a specific text label, such as "within" or "out", based on whether the absolute difference between two numbers is less than or equal to 20% of the baseline number.
Ensure your data columns contain numeric values formatted as numbers, and identify which column will act as your baseline reference for calculating the 20 percent threshold.
Use IF and ABS Functions to Calculate the Percentage Difference
This method uses the ABS function to calculate the absolute difference between two values and the IF function to return a specific text result based on whether the difference is within the 20% limit.
The ABS function is essential here because it ensures that both positive and negative differences (values above or below the baseline) are treated equally as absolute numbers. By multiplying the reference value by 20%, you establish the acceptable tolerance range.
Click on cell C1, or the first cell in the column where you want the "within" or "out" result to appear.
Type the formula =IF(ABS(B1-A1)<=20%*A1,"within","out") into the formula bar. In this example, A1 is the baseline reference value and B1 is the value being compared.
Press Enter to see the result for the first row. Then, click and drag the small green fill handle at the bottom-right corner of cell C1 down the column to apply the formula to your remaining data.
Easily Calculate Percentage Differences in WPS Spreadsheet
WPS Spreadsheet fully supports advanced mathematical and logical functions like IF and ABS, allowing you to accurately calculate percentage tolerances and automate data analysis just like you would in Microsoft Excel.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your spreadsheet document, and locate the two columns of numbers you want to compare.
- 2. Select the target cell: Click on the cell where you want the comparison result to be displayed.
- 3. Input the IF and ABS formula: Type =IF(ABS(B1-A1)<=20%*A1,"within","out") into the cell and press Enter.
- 4. Fill the formula down: Double-click or drag the fill handle in the bottom-right corner of the cell to automatically calculate the percentage difference for the rest of your rows.
Frequently Asked Questions
Can I change the 20 percent threshold to a different value?
Yes. You can easily adjust the threshold by replacing "20%" in the formula with any other percentage, such as "15%" or "50%". Alternatively, you can replace the "20%" with a cell reference (e.g., $D$1) that contains your target percentage, allowing you to update the threshold without editing the formula again.
How do I change the output text from "within" and "out"?
In the formula =IF(ABS(B1-A1)<=20%*A1,"within","out"), simply replace the words "within" and "out" with your preferred terms, such as "Pass" and "Fail" or "Acceptable" and "Review". Make sure to keep the double quotation marks around your custom text.
Why does the formula return an error when comparing empty cells?
If the reference cell (A1) is empty or contains text instead of a number, the mathematical calculation will fail, resulting in a #VALUE! error. Ensure that both columns contain valid numerical data before applying the formula.
