How to Create a Tiered Pricing or Fee Formula in WPS Office Excel
Question details
Create an Excel formula in WPS Office that uses a lookup table to calculate tiered pricing or fees, adding a 3% charge only above a 4,000 threshold and capping the result at 799.99.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a fee or pricing schedule where each tier has a base amount, an additional percentage applies above a threshold, and the final calculated value must not exceed a maximum.
- Observed behavior
- The formula needs to return the correct tiered result and enforce the 799.99 maximum limit.
Create a lookup table in J1:K6 with threshold values in column J and the corresponding base amounts in column K. Make sure H21 contains the value to be calculated before entering the formula.
Tiered Pricing Formula with VLOOKUP, MAX, and MIN
Use this when you need a single reusable formula that calculates the base tier amount, applies 3% only to the amount above 4,000, and caps the final fee at 799.99.
The formula uses VLOOKUP to return the base amount for the matching tier, MAX to ensure the percentage is only applied to the excess above 4,000, and MIN to cap the result.
Enter the tier thresholds in J1:J6 and the matching base amounts in K1:K6. In this example, the lookup table is $J$1:$K$6.
Select cell I21 or the cell where the result should appear, and type: =MIN(VLOOKUP(H21,$J$1:$K$6,2)+MAX(H21-4000,0)*0.03,799.99). Press Enter.
If you have values in H22, H23, and below, drag the fill handle down from I21 to apply the same calculation to the remaining rows.
Create Tiered Pricing Formulas in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same Excel functions and workbook structure, so you can build this tiered pricing formula with VLOOKUP, MAX, and MIN without changing your workflow.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook that contains the pricing or fee table.
- 2. Confirm the lookup table: Verify the threshold column and base amount column, such as $J$1:$K$6, are in place and sorted if you use approximate VLOOKUP.
- 3. Insert the formula: Click the result cell and enter =MIN(VLOOKUP(H21,$J$1:$K$6,2)+MAX(H21-4000,0)*0.03,799.99).
- 4. Fill and review: Drag the formula down for other amount rows and confirm the results stay at or below 799.99.
Frequently Asked Questions
What does MAX(H21-4000,0)*0.03 do in this formula?
It calculates the extra charge only on the amount above 4,000. If H21 is 3,800, MAX returns 0; if H21 is 5,000, it applies 3% to 1,000 only.
How do I change the 799.99 maximum cap?
Replace 799.99 in the MIN function with your desired maximum. For example, use 999.99 to cap the final result at 999.99.
Does VLOOKUP in this formula require an exact match?
If you use an approximate match, keep the threshold column sorted ascending. If your thresholds are unsorted, switch to FALSE for exact match or use a sorted lookup table.
Can I use this tiered pricing formula for more tiers?
Yes. Expand the lookup table in column J and K to include all thresholds and base amounts, then keep the VLOOKUP range updated to cover the new rows.
