Why the Excel IF and OR Formula Returns a 20 Percent Bonus
Question details
The user wants to understand why their combined IF and OR formula (=IF(OR(E8>1200,F8>3000),F8*0.2,F8*0.1)) calculates a 20% bonus even when only one of the two sales conditions is satisfied.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating sales bonuses based on multiple numerical conditions using Excel logical formulas.
- Observed behavior
- The formula returns the TRUE value (a 20% bonus) when only one of the conditions within the OR statement is met, rather than requiring both conditions to be true.
Check your company's specific bonus policy to confirm whether employees need to hit ANY of the targets (requires the OR function) or ALL of the targets (requires the AND function) to earn the 20% bonus.
Understand the OR Function Logic in Your IF Formula
The OR function evaluates to TRUE if any single condition inside it is met, which triggers the TRUE result of the IF statement.
In the formula =IF(OR(E8>1200,F8>3000),F8*0.2,F8*0.1), the OR function checks two conditions: whether E8 is greater than 1200, and whether F8 is greater than 3000.
By definition, the OR function only requires one of its arguments to be true. If either the first condition OR the second condition is met, the entire OR statement becomes TRUE. This causes the surrounding IF function to output the 'value_if_true' argument, which is F8*0.2 (the 20% bonus).
Click on the cell containing your combined IF and OR formula to make it active.
Look at the value in cell E8. If E8 is 1000, the condition E8>1200 evaluates to FALSE.
Look at the value in cell F8. If F8 is 4000, the condition F8>3000 evaluates to TRUE.
Because at least one condition (F8>3000) is TRUE, the OR function returns TRUE, resulting in the 20% bonus calculation.
Switch to the AND Function for Strict Conditions
If you want the 20% bonus to apply only when both sales targets are hit, replace the OR function with the AND function.
Calculate Complex Formulas Easily with WPS Office
WPS Spreadsheet fully supports advanced logical functions like IF, OR, and AND. You can seamlessly create, edit, and evaluate bonus calculation formulas with a user-friendly interface that is highly compatible with Microsoft Excel.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open your sales data workbook.
- 2. Enter the logical formula: Click on the target cell and type =IF(OR(E8>1200, F8>3000), F8*0.2, F8*0.1).
- 3. Use formula prompts: Watch for the helpful tooltip prompts that guide you through the logical test, true value, and false value parameters.
- 4. Drag to fill: Click and drag the fill handle at the bottom-right of the cell to apply this bonus logic to the rest of your dataset.
Frequently Asked Questions
What happens if both conditions in the OR function are FALSE?
If neither E8>1200 nor F8>3000 is true, the OR function returns FALSE. The IF function will then execute its 'value_if_false' argument, which in your formula is F8*0.1, resulting in a 10% bonus.
How can I add a third condition to my OR formula?
You can add multiple conditions inside the OR function by separating them with commas. For example, =IF(OR(E8>1200, F8>3000, G8="Yes"), F8*0.2, F8*0.1) will return a 20% bonus if any of the three conditions are met.
Why am I getting a #NAME? error when typing the IF OR formula?
The #NAME? error usually occurs if you misspell a function name (like typing 'IFF' instead of 'IF') or forget to put quotation marks around text criteria. Double-check your formula for typos.
