How to use the AND function with the IF function in Excel
The best way to describe how to use the AND function in excel is to give an example. Let's say that you have a list of Projects that have both a Value and a State where they are located. You might want to add another column that says whether or not you are interested.
Let's assume that you are interested in projects that have a value higher than $1500 AND that are located in your home state of Arizona. Using a combination of the IF function and the AND function you can "calculate" whether or not you are interested in a project.
So let's walk through that formula you will need in that last column.
First of all, we start with the IF function. You use IF to make your output conditional on certain parameters.
For an "everyday" example: IF you arrive before 5 pm the doors will be open, otherwise the doors will be closed.
The IF function has 3 inputs: a Logical Test, the Value if True, and the Value if False.
IF(logical_test, [value_if_true], [value_if_false])
Logical_test - This is the conditional statement. In the "everyday" example, it is "If you arrive before 5 pm".
Value_if_true - In the "everyday" example, it is "the doors will be open". This is the output if you arrive before 5 pm.
Value_if_false - In the "everyday" example above it is "the doors will be closed". This is the output if you arrive after 5 pm.
So, the IF function works great. However, in our initial example, with the projects, we have two conditions; not just one. And that is where the AND function comes in to play.
AND is a function that has one of two outputs. In computer speak it is known as a Boolean. A simple example of a Boolean is a light switch. It has only two conditions: off or on. A Boolean is either True or False.
AND has as many inputs as you want to include. They are: Logical1, logical2.. and so forth
AND(logical1, [logical2], ...)
Each one of the logical statements is just like the Logical_test in the IF function. The test was do you arrive before 5 pm or not. There are only 2 potential results: either you did (True) or you didn't (False). The AND function groups as many of those logical tests, as you want, together and says, If they are all True then I am True otherwise I am False.
So what we do is nest the AND function into the IF function in place of the Logical_test. Here we go.
if(and(logical1,logical2),value if true,value if false)
So, let's apply this to our initial problem with the projects. We want excel to tell us whether or not we are interested in a Project based on Its State (Logical1) and its value (Logical2). If it has a high enough value and is located in the right state, then we are "Interested" (Value if true). Otherwise we are "Not Interested" (Value if false).
Let's look at that in excel...
Within excel, in row 2 it is written:
On row 2 the State is "Arizona" and the Value is $1500, so the answer is...
The reason being that we are only interested if the value is greater than 1500 not equal to 1500. To include 1500 as a number that interests you, you would have to include an = sign next to the > sign like this:
Once you have the formula the way you want it, you can copy it into all the other cells in that column.
I hope this was helpful. If you have any questions, please post them as comments.
Have a happy day in Excel!