Excel IF Formula for Percentage Rates Based on an Amount Threshold
Question details
Select a percentage based on whether an amount is at or below 2,000 or exceeds it.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An amount needs one rate at or below 2,000 and another rate above 2,000.
- Observed behavior
- The formula should return the appropriate percentage or calculate separate portions if only the excess uses the second rate.
Enter a numeric amount in a cell, such as A2, and decide whether the selected rate applies to the whole amount or only to the portion above 2,000. Replace the example rates with your actual rates.
Use IF to select a rate for the whole amount
Use this formula when one percentage applies to the entire amount based on the threshold.
Type the amount into cell A2 in your Excel worksheet.
Select B2 and enter =IF(A2<=2000,5%,7%). The formula returns 5% for amounts at or below 2,000 and 7% for amounts above 2,000.
If you need the monetary result rather than the rate, enter =A2*IF(A2<=2000,5%,7%) in another cell. Adjust the cell reference and rates as needed.

Apply separate rates to the base and excess portions
Use this calculation when only the amount above 2,000 receives the second rate.
Apply an amount threshold formula in WPS Spreadsheets
Use WPS Spreadsheets to enter an IF formula that selects a rate based on an amount, then calculate the result in the same worksheet.
- 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
- 2. Enter the amount: Type the amount in cell A2.
- 3. Select the rate: Click B2, enter =IF(A2<=2000,5%,7%), and press Enter. Change the example rates as needed.
- 4. Calculate the result: Click C2 and enter =A2*B2 to apply the selected rate to the whole amount.

Frequently Asked Questions
What rate does the IF formula return for exactly 2,000?
The formula =IF(A2<=2000,5%,7%) returns 5% because the <= condition includes 2,000.
How do I calculate the actual percentage amount instead of displaying the rate?
Enter =A2*IF(A2<=2000,5%,7%) in a result cell. This multiplies the whole amount by the selected rate.
How can I apply the higher or lower rate only to the amount above 2,000?
For a nonnegative amount in A2, use =MIN(A2,2000)*30%+MAX(A2-2000,0)*20%. Replace the example rates with your own.
Why does my rate appear as 0.05 instead of 5%?
The values are equivalent. Select the formula cell and choose Home > Percent Style to display 0.05 as 5%.




