Excel OR and AND functions

Excel Or and AND functions: YOUR OBJECTIVE

Your company wants to reward its best salespersons with a trip to Paris. To win the trip, a salesperson’s sales in each reviewed month must be greater than the average sales in those months for all salespersons. To make such a determination easily, you need a function that can test to see whether several conditions are all met at the same time. Excel’s AND function can help you do this.

In this learning module “ Excel OR and AND functions”, you will be shown how to use Excel’s OR and AND functions.

 Excel OR an AND functions:HOW DOES IT WORK?

If you want insert a value that you specify yourself in a cell based on the state of two or more criteria, you can do so most conveniently using a combination of the IF and AND (or  excel OR) functions. Recall that you can nest functions, using another function as an argument in the initial function. In this learning object, you will be using the AND function as an argument in an IF function.

In the current example, a salesperson will be awarded a trip to Paris if his or her sales in each of the three months being considered exceed the average sales of all salespersons in the same time period. To make this determination, it is necessary that more than one condition be tested. In this case, the Logical_test argument of the IF function you will use can only produce a ‘true’ result, if all three conditions (monthly sales in January, February, and March are each greather than average sales) are met. To do this, you need to use a function that can determine whether multiple conditions are all met at the same time. The AND function can do this.

The following diagram indicates how all three conditions must be true for the IF function to act in accord with its Value_if_true argument:

To begin, click in the cell that is to display the final result, in our example, E4. Then start the Function Wizard by clicking the button just to the left of the formula bar. Now find and select the IF function in the Logical category. Click OK.

Because, in the current example, the IF function’s Logical_test argument must test more than one condition (the salesperson’s sales for each of three months must exceed average sales), you have to nest an AND function in the Logical_test input box. To do so, click in the Logical_test entry box, and then click the arrow next to the Name Box. In the selection list that appears, find and click the AND function (shown below).

Excel OR an AND functions: HINT

Whenever you nest one function in another function’s argument, you must either type in the nested function manually or insert it by choosing it from the list in the Name Box. Although intuitive, if you try to insert a function into the argument field of another function by clicking the button, the first function will be closed and dismissed.

Excel OR an AND functions: HINT

If the function you want to insert in another function’s argument field is not among those shown in the names list, click More Functions…, the last entry in the list. You can then choose a function from all available functions.

A new Function Arguments dialog box for the AND function will open.

Now enter the first of the three conditions into the Logical1 input box: B4>D13. Because all the conditions that will be used in column E refer to one value, i.e., the average sales, you should convert D13 to an absolute reference. Do so by pressing [F4].

Now enter the remaining two arguments (the other conditions) in a similar fashion.

Excel OR an AND functions:HINT

To find out more about the use of absolute references, refer to the learning module: Relative and absolute references.

After entering the 3 conditions for the AND function, you have to complete the IF function, which still needs the Value_if_true and Value_if_false arguments. To do that, click on IF in the formula bar (as shown below):

The Function Arguments dialog box will open again for the IF function. Here you can now see the AND function with its arguments in the Logical_test box:

To enter the remaining two arguments for the IF function, type “Yes” as the Value_if_true argument and “No” as the Value_if_false argument. Then click OK.

With E4 selected, the formula box now shows the completed function:

You can read this as follows: Determine whether the value in cell B4 is greater than the value in cell D13 and whether the value in cell C4 is greater than that in D13 and whether the value in D4 is greater than the one in D13. If all these conditions are true, insert a plain-text Yes into E4, otherwise insert a plain-text No into E4.

The result is displayed in cell E4. Now use AutoFill to copy the function down column E for the other salespersons. As a result of your use of the nested IF-AND function, column E clearly indicates which salespersons are to receive a trip to Paris and which will not.

Excel OR an AND functions:HINT

The learning module – AutoFill – explains how to use the AutoFill function for copying the contents of a cell to other, adjacent cells.

The syntax of the excel OR function is very like that of the AND function. Although this function’s arguments are also conditions, the excel OR function returns a value of TRUE if any of its arguments are true. In contrast, all argument of the AND function must be true for it to return a TRUE value.

If the excel OR function were to be used in the current function, a saleperson would receive a trip if his or her sales for just one of the examined months were greater than the average monthly sales.

If the excel OR function had been used, the complete function in the formula bar would look like this:

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen