How to Use the IF(OR) Formula in Excel

The IF function can be used to perform a logical comparison between two values by testing for a condition and returning the result based on whether that condition is TRUE or FALSE. You can also use the OR function, one of the logical functions, in combination with the IF function to determine if any conditions in a test are TRUE or FALSE.

Formula Explanation:

• Logical_test (required): The condition you want to test.
• Value_if_true (required): The value that you want returned if the result of logical_test is TRUE.
• Value_if_false (optional): The value that you want returned if the result of logical_test is FALSE.

Example:

The General Manager of a second-hand car dealership wants to know which salespersons met any of the sales targets. If the salespersons meet at least one sales target, they are eligible for the month end bonus.

The two criteria are:

a) Units sold during the month must exceed 6 cars.

b) Total sales $ must be greater than $10k for the month.

The Finance Analyst produces a sales report from the ERP system with sales by
salesperson for the current month and the report provides details on the number of
sales made and total sales dollars.

Solution:

Based on the below analysis, both Erika and Evan have met at least one of the two sales performance criteria. Therefore, they are both eligible for the month-end bonus.

Erika has sold over $10k of cars and 8 units while Evan has only sold $6.5k of cars but has met the minimum number of cars sold quota of 6 by selling 7 during the month.

• When we hit the “Enter” key for the above formula, we get $600 for the bonus of Erika because she has met at least one of the conditions. We can drag that formula down and apply it to the rest of the table for the salespersons.
• We noticed that Evan is eligible for the bonus as well since he has exceeded the goal per unit sales (7 units) which is an excess of the minimum amount to be sold of 6.
• The rest of the individuals are not eligible for the bonuses because their total unit sales have not exceeded six for the month and the gross amount of sales they closed for the month is less than $10,000.

Here,

I. Logical_test (OR) formula was used to set a condition that ANY of the criteria should be met before a value will be set as “true”.

II. Value_if_true formula was used to calculate the “Bonus” if any of the criteria was satisfied.

III. Value_if_false formula was used to set the cell as “0” (Zero) if both of the criteria was not satisfied.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!