AND Function

What is the AND Function?

The AND is categorized as a logical function that returns TRUE when two are more conditions are fulfilled and FALSE when either of the conditions fails to be fulfilled.

Suppose that you have a number that is equal to 20. Let's say A = 20. There are two other numbers, i.e., B = 40 and C = 10, in the spreadsheet.

We want to evaluate whether the value of A is less than B and greater than C. You could probably write these conditions in different cells, but what if you wanted them in the same cell?

In this case, we input the condition as AND(A < B, A > C), to which Excel returns the result as TRUE since both the conditions are fulfilled, i.e., 20 is smaller than 40 but greater than 10.

You can even include the IF statement along with the function to get a customized result when the process evaluates to TRUE and another result when it evaluates to FALSE.

Since the function is logical, comparison operators are something that you must understand before we proceed to the next section.

The logical operators play an essential role in specifying different criteria in the function. In addition, these comparison operators help to compare numbers and strings and perform complex assessments of the conditions.

Listed below are the logical operators that you can use while using the function:

 

The syntax for the AND function:

=AND(logical1,[logical2])

where,

• logical1 = (required) The first logical condition which will be evaluated 
• logical2 = (optional) The second logical condition which will be evaluated

You can use up to 255 additional logical values in the function that increases the scope of your analysis.

Understanding AND Function

When you go on a vacation, you create a checklist of what you need. For example, suppose you are traveling on day 1; you would need an airline ticket, a bag full of essentials, clothes, and the booking at the hotel where you intend to stay once you reach the destination.

All three things are essential, and compromising on either can make your vacation unpleasant.

Only when you have all three things on day 1 of the vacation can you usually expect the holiday to go?

It works similarly, evaluating multiple conditions and returning TRUE when all of them are fulfilled and FALSE when even one of the conditions fails.

The function forms the backbone of the Excel community by allowing you to input more than one logical condition and evaluate it to fulfill those conditions.

The OR function is another function that allows the evaluation of multiple conditions. Still, the significant difference between both functions is that OR will return TRUE even when one of the conditions is fulfilled and FALSE only when all the conditions fail.

Excel Logical functions are the category under which the AND Function falls. It is employed to ascertain whether the test's specified requirements are TRUE. The function can be used, for instance, to determine whether a value in cell B1 is more than 50 and less than 100.

The function is helpful to a financial analyst when verifying that several conditions are met, particularly when checking that several conditions are met. It also saves us from having additional layered IFS, and it allows us to mix the AND and OR functions.

How to use the AND function

It can be used in two ways - from the functions library or as a worksheet formula.

a. Method#1: From the function's library

You can quickly get the result by inputting two or more conditions by using the predefined functions from the functions library. To use the position, please follow the steps below:

a. First, select the cell in which you intend to get the result.

b. Click on the Formulas tab > Logical drop-down in the function's library and select the AND function.

c. This should open up the dialog box, as illustrated below:

d. Here, you input all the conditions that you want to evaluate altogether. For example, suppose you have three text strings 'Apple,' 'Mango,' and 'Guava.' You need to check whether all these strings have five characters.

The conditions that you would input will be

e. We even get a preview for the result, which says 'TRUE,' stating that all of our conditions meet. If you click on Ok, you will get the same effect in the select cell, 'TRUE.'

f. Suppose you change the logical condition to LEN("Mango")=6. What do you think the final result would be?

g. In that case, the result would be FALSE since one of the conditions did not fulfill the criteria.

b. Method#2: As a worksheet formula

Using the function as a worksheet formula is more straightforward than the earlier method. All you need to do is begin with an equal sign and input the function name and the conditions inside the parenthesis.

Suppose you have the three text strings 'Apple', 'Mango', and 'Guava.' First, you must evaluate whether the text strings' character length matches.

The formula that we will use to evaluate the text strings is =AND(LEN(C2)=5, LEN(C3)=5, LEN(C4)=5), which should give you the result as illustrated below:

Since all of our conditions are fulfilled, we get the result as TRUE.

Examples Of AND Function

The AND function is versatile and works phenomenally with most positions in the functions library. 

Be it the logical or the lookup functions, AND can play a pivotal role in taking the data analysis one step further.

For example, the IF statements are one of the most used functions in Excel. If you need to evaluate a cell value based on multiple conditions, out of which all must be met, then you can nest the AND function inside the IF statements.

Similarly, the function can be used in collaboration with VLOOKUPs to check if a particular value exists in multiple tables. If the result returns as TRUE, then the value exists in two or more tables or is missing in either of those tables.

This section will show different situations where you can use the function and make the best use of it.

Example #1

Dates are something you would end up comparing many times in your Excel models.

Suppose that you are one of the Amazon resellers and need to evaluate all the orders that are processed but were unfortunately returned to you. 

The Date 1' column represents the delivery date for the orders, while the 'Date 2' represents the return date for the products.

Your organization allows a 15-day no-fee refund policy. However, if the purchase was made more than 15 days ago, then there is a 20% reduction in the refund.

The data looks as illustrated below: 

Let's say that today's date is 7/3/2022. Here, we can use the combination of the TODAY, IF, and AND functions to evaluate the conditions.

The formula that we will use is =IF(AND(B3<TODAY(),C3<B3+15),"100% Refund","80% Refund"), which will give you the result, as illustrated below:

So how does the formula works?

We first evaluate that the order is not the current but rather an old order. Just a check to ensure we have the relevant data. The other logical condition is to check if the return date is less than the order date + 15 (the time limit for a 100% refund).

Any two dates that fulfill both the conditions will return the result as TRUE, whose value is 100% Refund, while if the statements return as FALSE, the result will be 80% refund.

Example #2

Suppose you day trade for a living. You identify the good stock with high potential for price movement for the day and make the trades, as illustrated below:

Let's say, from the data, and you need to find the trades where you bought Tesla Inc for more than $25000.

This time we have three conditions that we have to set up:

• Tesla stock

• Buy condition

• Amount more than $25,000.

Here as well, we will use the combination of IF and AND functions so that the formula becomes =IF(AND(C3= "Tesla Inc", D3= "Buy", E3>25000), "Trade Identified"," "), which will give you the result:

As you can see, only in two instances all the conditions are fulfilled, which then evaluates the AND function to TRUE. When it does so, the IF function returns the 

result as 'Trade Identified' for TRUE and returns an empty string for FALSE.

Example #3

We have already stressed how friendly the AND function is towards other parts of the library.

If one of the conditions you input is a MAX function while the other is VLOOKUP, and if both the conditions are fulfilled, the function would evaluate to TRUE.

Let's see an example to understand it better. Suppose you have the test scores for different students, as illustrated below:

You need to identify if the student in cell H5 is a topper in his math examination and scored passing marks in all other tests.

You might have already guessed what functions we would use - the VLOOKUP, MAX, and the AND function.

Now, let's understand our conditions first. The passing score on all the tests is greater than 35. It's that simple. 

The final primary condition is that the test score returned for the student should be the maximum for the subject so that he can be declared a topper. This is where the MAX function plays a pivotal role.

To get the subject topper, we will use the formula

=VLOOKUP(H5, B2:F12,4, FALSE)=MAX(E3:E12),

which should give us the result as TRUE or FALSE. 

After looping through a couple of student names, we determined that Justin Patterson is the 'Math' topper.

Now all that remains is the other condition to check whether the student has scored higher than 35 on all their test.

Here, we will combine our previous formula as well as add a couple more VLOOKUPs along with IF so that the final formula becomes =IF(AND(VLOOKUP(H5, B2:F12,4, FALSE)=MAX(E3:E12), VLOOKUP(H5, B2:F12,2, FALSE)>35, VLOOKUP(H5, B2:F12,3, FALSE)>35, VLOOKUP(H5, B2:F12,5, FALSE)>35), "Topper! 'and' passed all exams", "Not a topper or failed one of the exams"), to give the result as illustrated below:

If you change the student name to 'David Vincent', we get the result:

Some of you might find it frustrating that we can only see the result for one value rather than all the students together.

In this case, you can create an additional column as below:

Finally, all you need to do is input a somewhat similar and simplified formula which is, =IF(AND(E3=MAX($E$3:$E$12), C3>35, D3>35, F3>35), "Topper! 'and' passed all exams", "Not a topper or failed one of the exams"). 

It gives you the result as illustrated below:

In the earlier formula, we used VLOOKUP so that Excel could easily find the corresponding values. However, in our more straightforward recipe, all the conditions were input by making a direct comparison with the test scores.

Yes, simplicity is effective in the long term, but it won't harm you if you try and combine different functions to get similar results. In addition, it gives you a different perspective to understand the data in question and probably build a leaner Excel model.

AND vs. OR function

The OR function is similar to its counterpart because it helps the user input multiple conditions but differs based on the result of the function it returns. 

If even one of the conditions is met, then the function evaluates to TRUE, while it would only evaluate to FALSE when all the conditions fail to satisfy.

The syntax for the function is

=OR(logical1,[logical2])

where

logical1 = (required) The first logical condition which will be evaluated

logical2 = (optional) The second logical condition which will be evaluated

You can use up to 255 additional logical values in the function that can increase the scope of your analysis.

Suppose we have the test scores for the students, as illustrated below:

We want to find whether a student has scored more significant than 80 in their test scores. Therefore, when we use the AND function, the result would only evaluate TRUE when all the test scores exceed 80.

The formula that we will use in cell G3 is =AND(C3>80,D3>80,E3>80,F3>80). After dragging it down to cell G12, we will get the result: 

Even though we have some of the test scores greater than 80(for example, Chad Moore scored 84 on the History test), we still get the result as FALSE. As the rest of the conditions are not fulfilled, we ultimately return the FALSE.

On the other hand, when you use the formula =OR(C3>80, D3>80, E3>80, F3>80), you will get the result as illustrated below:

Seven out of ten times, we got the result as TRUE, meaning one of the tests had a score greater than 80. The rest three times, non of the test scores were greater than 80, and hence we got the result as FALSE.

The disparity in the ability of both functions has pros and cons based on the situations in which we use either process.

Still, both functions will fast-track your progress toward becoming an Excel wizard!

Researched and authored by Akash Bagul | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn