Formula Errors

Excel IF Formula for Percentage Rates Based on an Amount Threshold

Amos GikundaAmos Gikunda Nov 18, 2026 869 views

Question details

Select a percentage based on whether an amount is at or below 2,000 or exceeds it.

Excel IF Formula for Percentage Rates Based on an Amount Threshold
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the amount

Type the amount into cell A2 in your Excel worksheet.

2
Enter the IF formula

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.

3
Calculate the percentage amount

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.

Use IF to select a rate for the whole amount
Display the rate as a percentage: If B2 displays 0.05 or 0.07, select B2 and choose Home > Percent Style.
Calculate threshold rates in WPS Spreadsheets

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. 1. Open a spreadsheet: Open WPS Spreadsheets and create a workbook or open an existing Excel file.
  2. 2. Enter the amount: Type the amount in cell A2.
  3. 3. Select the rate: Click B2, enter =IF(A2<=2000,5%,7%), and press Enter. Change the example rates as needed.
  4. 4. Calculate the result: Click C2 and enter =A2*B2 to apply the selected rate to the whole amount.
Select a percentage rate using an IF formulaOpen and edit Microsoft Excel XLSX and XLS filesCalculate separate base and excess portions with MIN and MAXUse a familiar spreadsheet interface
microsoft office alternative - wps office

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%.