How to Create an Excel IF Formula for a Tiered Sales Bonus Plan in WPS Office
Question details
The user needs a spreadsheet formula that compares total sales in L57 with the goal in L56 and calculates a tiered bonus based on how many units sales exceed the goal.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a sales bonus model where commission increases in tiers based on unit performance.
- Observed behavior
- The formula should automatically return the correct tiered bonus: 0 below goal, $500 at goal, $250 per five-unit tier up to 29 units over, and $3,000 plus $100 per unit for 30 or more units over.
Confirm that L56 contains the sales goal, L57 contains total sales, and both cells contain numeric values rather than text. Open WPS Office Spreadsheets and verify that the IF and QUOTIENT functions are available on the Formulas tab.
Use a Nested IF Formula with QUOTIENT for Tiered Bonus Payouts
Use this when you need one compact formula that handles all bonus tiers in a single cell and matches typical Excel tiered bonus logic.
The outer IF returns 0 when L57 is below L56. When the excess is less than 30, QUOTIENT(L57-L56,5) counts complete five-unit tiers and adds $250 for each tier on top of the $500 base. If the excess is 30 or more, the formula pays $3,000 plus $100 for every additional unit.
Open the workbook in WPS Office Spreadsheets and click the cell where you want the tiered bonus to appear.
Type the following formula exactly, then press Enter: =IF(L57<L56,0,IF(L57-L56<30,500+250*QUOTIENT(L57-L56,5),3000+100*(L57-L56-30)))
Change the value in L57 to test below goal, exactly at goal, at each five-unit tier, and at 30 or more units over goal to confirm the bonus updates.
Use an IFS Formula as a More Readable Tier Table
Use this if you prefer a formula that lists each five-unit tier explicitly and is easier to audit for non-standard tier boundaries.
Create the Tiered Bonus IF Formula in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same IF, IFS, and QUOTIENT functions as Excel, so you can apply this tiered sales bonus formula directly and keep your .xlsx workbook calculations intact.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the Excel workbook that contains L56 and L57.
- 2. Select the result cell: Click the cell where you want the calculated bonus to appear.
- 3. Enter the formula: Type the nested IF formula from this article and press Enter to calculate the tiered bonus.
- 4. Verify the bonus tiers: Test L57 with values below L56, equal to L56, at each five-unit tier, and above 30 units over goal.
Frequently Asked Questions
Why does my IF formula return 0 for a sales amount that should earn a bonus?
The formula returns 0 when L57 is less than L56. If that is not expected, check that L56 contains the correct goal, L57 contains the actual sales number, and both cells are formatted as numbers rather than text.
How do I change the $500 base bonus in the tiered formula?
Replace the number 500 in both the middle and upper parts of the formula. For example, change 500+250*QUOTIENT(L57-L56,5) to 600+250*QUOTIENT(L57-L56,5) if the at-goal bonus should be $600.
What does QUOTIENT do in this sales bonus formula?
QUOTIENT(L57-L56,5) divides the excess units by 5 and returns only the whole-number result. This counts complete five-unit tiers, so each tier adds $250 to the $500 base bonus.
Can I use this Excel IF formula in WPS Office without changing it?
Yes. WPS Office Spreadsheets supports IF, IFS, and QUOTIENT and opens Excel .xlsx workbooks, so the same formula can be pasted into WPS Office and calculated normally.
What if my bonus plan pays differently above 30 units over goal?
Edit the upper-tier part of the formula, 3000+100*(L57-L56-30). Replace 3000 with your upper-tier base amount and 100 with your per-unit amount for units beyond 30 over goal.
