How to use logical functions AND OR in Excel formulas

The AND function is a logical function. It allows you to define the conditions that it checks for the result TRUE if all conditions are met (FALSE if at least one condition is not met). The OR function is also a logical function, which, unlike "AND", determines only one of the available conditions for the result TRUE (FALSE - if all the conditions being checked are not met).



Boolean functions in Excel practical work

Concept of AND OR, the syntax for both functions is as follows:

Boolean value 1 is a required argument, and the number of subsequent ones depends on the task. Now let's see how these functions work. We have a form about the results of tests that were conducted among employees and the passing score is indicated. We need to find out which of the employees coped with all the tests and who did not. To do this, we indicate in the formula that we will check AND the first test, AND the second, AND the third, AND the fourth, each must be at least 7 (cell D8):

As a result, in cell G3 we got the result TRUE, since all conditions are met. Be sure to use absolute references and copy the formula to the end of the column. Now we have the result for all employees:

Since we have the value 6 in cells E4, F4 and F6, the condition was not met and the result FALSE was returned and we found out which of the employees failed at least one test, and who coped with all and can continue to work. But the words "TRUE" or "FALSE" do not quite correctly reflect the meaning of the results. Therefore, we add two more expressions to the function - "Perfect" will be used if TRUE, and "Have a retakes" will be used if FALSE. The IF function will help us with this, in which we will embed the AND function:

Copy the function to the end of the column and get the finished table:

Opposite the cells, the value of which is less than 7, we got the result “Have a retakes”.

Now let's look at how the OR function works. We still have the same tests and employees, but now we need to check if we have an excellent one in at least one test. That is, is there a value of 10 among the values of cells C19, D19, E19, F19:

We got the value TRUE because the value of cell C19 is 10. Now we copy our formula to the end and get the result for the rest of the employees:

For more correct informativeness, we will replace the result “TRUE” with the phrase “With 10s”, and the result “FALSE” with the phrase “Without 10s”. To do this, we also use the IF function, in which we put the OR formula:

In cell C26, we have the value 10, so the formula returned the value if TRUE - "With 10s." Copy the formula to the end of the column and get the finished table:



Using AND OR functions instead of nested IF functions

Sometimes, using AND/OR functions, nested IF functions can be avoided. Nested functions are a good tool, but they become unwieldy when new conditions are added. For example, our task “Determine which employees completed all the tests and who needs to improve them” can be executed with nested IF functions:

In cell G33, we built a nested function and copied it to the end of the column. We got the same results as using IF + AND, however, the formula itself is very long, it will be easy to get confused in it if we need to add new conditions. In addition, working with it will take more time, although they perform the task correctly and in the same way. Therefore, it is better to give preference to the IF function using the AND function:

The second example, where we used IF+OR to find which employee got the highest score, we can also consider using nested IF functions:

We got the same results. However, if we compare the formulas of these two examples, the IF+OR function is much shorter and more accurate, especially when new conditions are added:

How combinations IF+AND+OR, IF+OR+AND work

Sometimes AND, OR, IF functions are used simultaneously. Before delving into some of these examples, let's reinforce the use of formulas we already know. We have budget information for various departments. We want to check if the budget amount of a certain department exceeds the value of 100,000 AND if the absolute expenditures since the beginning of the year exceed the value of 50,000. If these two conditions are met, we will indicate the word “Done”, and if not, the sign “-”. Specify absolute references for cells with conditions, otherwise when copying the formula, the values will also be omitted:

We got a "-" result because only one condition was met - 70,000 is greater than 50,000, but 80,000 is not greater than 100,000. Let's copy our formula to the end of the column and determine which departments meet the conditions:

In this example, we have specified certain conditions that must be met at the same time. Now let's look at an example where we expand the functionality of the formula by adding the OR function. Let's leave the two conditions known to us from the previous example - the budget exceeds 100,000, the absolute expenses exceed 50,000 and add new conditions through the OR function: was there a budget overrun last year OR was the budget overrun by more than 20%. Now we have 4 conditions, but only if three of them are true - we will get the value "Done":

We used a combination of IF + AND + OR functions. Let's copy the formula to the end of the column and see the departments whose data match the conditions we specified:

The production department and R&D met our conditions because the budget exceeds 100,000 AND the absolute expenses exceed 50,000, in addition, the R&D department has an over budget of more than 20%, and the production department has an over budget, but less than 20% - this is enough to comply with our terms and conditions.

Now let's search for departments that will match one of the given conditions. The principle of operation is the same as in the example with the fulfillment of the conditions “Budget is more than 100,000 AND absolute expenses are more than 50,000”, but this time we will change the requirements - we need departments that fulfill AT LEAST one requirement: budget is more than 150,000 or absolute expenses more than 160,000. The IF + OR functions will help us with this:

We copy the formula and see that the status "Done" was received by those departments that satisfied one of the requirements:

Finally, consider the last combination of IF + OR + AND functions. In this case, we need to add the last AND function as the third condition of the OR function. For the AND function, we will indicate two requirements - the presence of an excess of the budget, an excess of more than 10%:

This combination matches AT LEAST one of the three requirements:

1. Budget over 150,000.
2. Absolute expenses are more than 160,000.
3. The presence of an excess of the last year's budget, the excess indicator is more than 10%.

Copy the formula to the end of the table and check which departments have met our requirements:

Download logical functions AND OR in Excel with examples

This is how logical functions work in Excel using additional formulas to solve multi-tasking problems.