How To Use OR Function In Excel

How to use OR function in Excel? There might be some functions that you don’t know how to operate and for this, you need some guidance and help. The function “OR” is found in the Formulas Tab under Logical. It checks whether any of the arguments are true and will yield either a TRUE or A FALSE value.

The syntax or formula is = OR (logical1, logical2, logical3,…)

The OR Function excel can be used with Logical Operators, nested within an IF Function, and used in conjunction with the AND or NOT Functions.

The syntax or formula is

Logical Operators

It’s the end of the year and employee bonuses are being distributed. The first bonus is a Sales Bonus and it is awarded to every employee who either had over $10,000 in sales or sold more than 10 Protection Plans.

1. Logical1 =B2>10000
2. Logical2 =C2>10
3. =OR(B2>10000,C2>10)
4. Copy the formula down Column F
5. All employees who have a TRUE will get the Sales Bonus this year

IF Function

To change the value in Column F that shows the actual bonus ($250.00) instead of the TRUE or FALSE values use an IF Function.

1. Insert the OR Function excel from the example above into an IF Function as the Logical_test.
2. IF (OR (B2>10000, C2>10), “$250”, “0”)
3. The function reads, If the employee has sales exceeding $10,000 or has sold more than 10 protection plans return $250, if not $0.
4. Copy down the column and now you can see exactly who gets a bonus.

AND Function

AND function is used when there are multiple criteria, for example, a training bonus of $25 is issued to all employees who had either 8 hours or 16 hours only if the employee was absent for less than 2 days.

1. Enter the OR Function in excel as = OR (D2=8, D2=16)
2. Nest the OR Function into an AND Function as = AND (OR (D3=8, D3=16), E3<2)
3. Nest the AND Function into the IF Function as = IF (AND (OR (D4=8, D4=16), E4<2), “$25”, “$0”)
4. The final formula reads If Training Hours are equal to 8 or 16 AND Days Missed is under 2, then return a value of $25 if not, no bonus is awarded.

How to Use OR Function & NOT Function?

The NOT Function changes FALSE to TRUE and TRUE to FALSE. When combined with the OR Function in excel it searches for values that are NOT this or that. The Company gives gift cards out at the end of the year.

Employees who did not receive a Training bonus or a Sales bonus will receive a $10 Gift Card as a thank you from the Owner.

1. Create the OR Statement = OR (F2=”$250″, G2=”$25″), a TRUE Value means the employee received a Sales bonus and/or a Training bonus.
2. Nest the OR Statement into a NOT Function = NOT (OR (F2=”$250″, G2=”$25″))to search for those that didn’t receive either bonus.
3. Nest the NOT Function inside an IF Function                                              = IF (NOT (OR (F2=”$250″, G2=”$25″)), “$10”, “”)this function reads, if the employee didn’t receive either bonus, place a $10 in the cell, if they did receive a bonus leave the cell blank (told by the “” in the value_if_false element).

Multiple Nests

Continuing with the above example, let’s say employees who received one bonus receive a $25 gift card, and those that earned both bonuses receive a $100 gift card at the end of the year.

1. Create an AND Function = AND (F2=”$250″, G2=”$25″)to check if the employee received both bonuses.
2. Nest the AND Function within an IF Function IF (AND (F2=”$250″, G2=”$25″), “$100”, “$25”)to say if they did receive both return a value of $100, if not return a value of $25.
3. Nest the IF Function above into the value_if_false condition of the original IF function IF (NOT (OR (F2=”$250″, G2=”$25″)), “$10″, IF (AND (F2=”$250″, G2=”$25”), “$100”, “$25”))
4. The logic in this very large function is first to check if the employee did not receive a bonus and leaving a $10 value, if they did it is not looking to see if they received both and leaving a $100. The only other option is for the employee to receive one bonus and in that case, it is leaving a value of $25.
5. Working backward is the best way to ensure your Nested Functions remains intact and operable. In the above worksheet, I test each function one by one in Column J. If the function worked properly, I delete the equal sign from the front. Go to the next row and copy the function into the next logical test. The individual functions are colored to show how they fit within each other.