Formula Errors

How to Use Excel IF Formula for Different Deductions Based on Thresholds

Adam Davis Sep 27, 2026 869 views

Question details

The user needs a single formula to deduct varying amounts from a cell value depending on which numerical threshold the value exceeds.

How to Use Excel IF Formula for Different Deductions Based on Thresholds

Product: Excel
Device & OS: not provided
Scenario: Calculating tiered deductions such as taxes, fees, or discounts based on multiple monetary thresholds within a single cell.
Observed behavior: The formula needs to output the original value minus the correct deduction amount depending on whether the value surpasses specific breakpoints like $15,000 or $10,000.

Before you start

Ensure the cell you are referencing contains numerical values, and check whether your computer's regional settings require commas or semicolons as formula argument separators.

Solution 1Recommended

Use a Nested IF Formula Starting with the Highest Threshold

This method evaluates multiple conditions in a single cell by ensuring the largest threshold is checked before the smaller ones.

When using a nested IF function to calculate tiered thresholds, the order of logic is crucial. Excel evaluates IF functions from left to right and stops at the first TRUE condition. If you check the lower threshold first, the formula will trigger prematurely and ignore higher values entirely. Always start with the largest threshold.

Select the result cell

Click on the empty cell where you want the final calculated deduction result to appear.

Enter the nested IF formula

Type the formula =IF(B13>15000, B13-2800, IF(B13>10000, B13-1200, B13)) into the formula bar. Replace 'B13' with the cell reference that contains your original value.

Apply and drag to fill

Press Enter to apply the formula. If you have multiple rows of data, click and drag the fill handle at the bottom-right corner of the cell to copy the formula down the column.

Use a Nested IF Formula Starting with the Highest Threshold

Regional Settings Warning: If you receive a formula error, your region may use a comma as a decimal mark. In this case, use semicolons instead of commas to separate arguments: =IF(B13>15000; B13-2800; IF(B13>10000; B13-1200; B13)).

Effortless Spreadsheet Calculations

Calculate Tiered Deductions Easily with WPS Office

WPS Spreadsheet fully supports advanced Excel formulas, including nested IF functions, making it simple to calculate complex deductions and thresholds for free.

1. Open your data: Launch WPS Office and open your spreadsheet containing the threshold values.
2. Enter the formula: Select the target cell, type '=' to start your nested IF formula, and follow the syntax tooltips.
3. Calculate instantly: Press Enter to calculate the exact deduction amount based on your specified thresholds.

100% compatible with Microsoft Excel formulas and .xlsx formatsBuilt-in formula suggestions, autocomplete, and error checkingLightweight, fast, and completely free to use for everyday tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my nested IF formula calculating the highest deduction properly?

This usually happens if the logical tests are placed in the wrong order. An IF formula stops running as soon as it finds the first TRUE condition. You must place the highest threshold (e.g., >15000) before the lower ones (e.g., >10000).

Can I use the IFS function instead of nested IFs for thresholds?

Yes, if your version of the spreadsheet software supports the IFS function, you can write =IFS(B13>15000, B13-2800, B13>10000, B13-1200, TRUE, B13) to achieve the exact same result without nesting.

Why am I getting a syntax error when copying the formula?

Syntax errors often occur due to regional computer settings. If your region uses a comma to represent decimals, your spreadsheet requires semicolons (;) to separate formula arguments instead of commas (,).

Excel & Data Problems