Fix an Excel IFS Formula for a Result Between 3 Million and 5 Million
Question details
Correct an IFS formula so it returns the value of D22-D18 only when that result is between 3,000,000 and 5,000,000.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to test the calculated difference between cells D22 and D18 against a numeric range.
- Observed behavior
- The original formula uses unnecessary one-item array constants and may use an incorrect cell reference, which can produce an unexpected result or #N/A.
Confirm that D22 and D18 contain numeric values, not numbers stored as text. Decide whether exactly 3,000,000 and 5,000,000 should be included in the allowed range.
Return D22-D18 only when it is strictly between 3,000,000 and 5,000,000
Use this formula when the result must be greater than 3,000,000 and less than 5,000,000; the two boundary values are excluded.
The formula calculates D22-D18, checks both range conditions with AND, and returns the numeric calculation when both conditions are true. Do not wrap values such as 5000000 in array constants like {"5000000"}, because an array result is not needed.
Click the cell where you want the range-checked result to appear.
Type or paste =IFS(AND(D22-D18>3000000,D22-D18<5000000),D22-D18) and press Enter.
Verify that both parts of the calculation use D18. Replace any unintended reference to D17 if the calculation is meant to be D22-D18.
Include 3,000,000 and 5,000,000 in the allowed range
Use this version when results equal to either boundary should also be returned.
Use WPS Spreadsheets to test a calculated value within a range
WPS Spreadsheets lets you enter the same IFS, AND, and comparison formulas to calculate D22-D18 and return the value only when it meets your selected range conditions.
- 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing cells D18 and D22.
- 2. Select the output cell: Click the cell that should display the result of the range check.
- 3. Enter the formula: For an inclusive range, enter =IFS(AND(D22-D18>=3000000,D22-D18<=5000000),D22-D18,TRUE,"").
- 4. Confirm the result: Press Enter, then change or review the values in D22 and D18 to verify that the formula returns the expected result.
Frequently Asked Questions
Why does my IFS formula return #N/A when the result is outside the range?
IFS returns #N/A when none of its conditions evaluate to TRUE. Add a final condition such as TRUE,"" or TRUE,0 to provide a fallback result.
Should I use > or >= in an IFS range formula?
Use > and < to exclude 3,000,000 and 5,000,000. Use >= and <= to include values exactly equal to those limits.
Do I need braces around numbers in an Excel formula?
No. Braces such as {"5000000"} create an array constant and are unnecessary for a single numeric comparison. Use 5000000 directly.
Why should the formula return D22-D18 instead of a quoted value?
Returning D22-D18 keeps the result numeric, allowing it to be summed, compared, or used in other formulas. A quoted value such as "5000000" is text.
How can I check whether the formula references the wrong cell?
Click the formula cell and inspect the formula bar. If the intended calculation is D22-D18, replace any accidental D17 reference with D18.
