How to Use IF and OR to Calculate an Excel Bonus in WPS Spreadsheets
Question details
The user needs to calculate a bonus in a spreadsheet by using IF and OR so that the formula returns 10% of operating profit when either a sales target or an operating profit target is met, and zero otherwise.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is building a bonus calculation worksheet and needs a conditional formula based on two separate target cells.
- Observed behavior
- The desired behavior is for the formula to return 0.1*D9 when D4>=D13 or D9>=D14, and 0 when neither condition is true.
Open the workbook in WPS Spreadsheets and confirm which cells contain the sales target, operating profit target, and operating profit amount before entering the formula.
Create the bonus formula with IF and OR
Use this when you need a single formula that pays a bonus if at least one of two targets is reached.
The formula =IF(OR(D4>=D13,D9>=D14),0.1*D9,0) checks whether either the sales target in D13 or the operating profit target in D14 is met. If true, it multiplies the operating profit cell D9 by 10%; otherwise it returns 0.
Click the cell where the bonus amount should appear, such as D15 or another empty cell outside your data range.
Type =IF(OR(D4>=D13,D9>=D14),0.1*D9,0) into the formula bar or directly into the cell.
Press Enter to calculate the result. WPS Spreadsheets will display 10% of the operating profit in D9 when at least one condition is true.
If your sales, target, and profit cells are in different locations, replace D4, D13, D9, and D14 with the correct cell references for your workbook.
Calculate an Employee Bonus with IF and OR in WPS Spreadsheets
Use WPS Spreadsheets to apply the same IF and OR logic as Excel, returning 10% of operating profit when either the sales target or operating profit target is reached.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the Excel-compatible workbook that contains your target and profit data.
- 2. Select the result cell: Click the cell where the bonus amount should be calculated.
- 3. Enter the formula: Type =IF(OR(D4>=D13,D9>=D14),0.1*D9,0) and adjust the cell references to match your own worksheet.
- 4. Press Enter: Press Enter to calculate the bonus and display the result immediately.
- 5. Review the result: Check the returned value and update the target cells or percentage to test different bonus scenarios.
Frequently Asked Questions
What does the OR part do in the bonus formula?
The OR function checks whether either D4>=D13 or D9>=D14 is true. If at least one condition is met, IF returns the bonus; otherwise it returns 0.
Can I change the bonus from 10% to another percentage?
Yes. Replace 0.1 with the decimal form of your percentage, for example 0.15 for 15%, inside =IF(OR(D4>=D13,D9>=D14),0.15*D9,0).
What should I do if my target cells are different?
Replace D4, D13, D9, and D14 with the actual cells in your worksheet. Select the formula cell and edit the references directly in the formula bar.
Does this formula work in Microsoft Excel and WPS Spreadsheets?
Yes. The IF and OR formula syntax is supported in both WPS Spreadsheets and Excel, so the same formula can be used in .xlsx workbooks.
How can I test whether the formula is correct?
Enter values that meet only one target, both targets, or neither target, and confirm the result changes between 10% of D9 and 0.
