A to Z of Excel Functions: AND

Today we’re going to look at the AND function in Excel.  AND is one of a few logical functions in Excel – that is they return either a TRUE or FALSE value based on their input.  It’s especially useful in formulas where you want something to happen based on more than one condition being TRUE.

So, how does it work?  It’s actually a really simple function and looks like this:-

=AND(logical test 1, logical test 2…..and so on)

At its simplest it looks at all of the conditions in ‘logical test’ and returns its TRUE or FALSE value based on whether they all evaluate TRUE or not.

Let’s have a look at it working at its simplest:-

=AND(TRUE, TRUE, TRUE)
=TRUE

In the example above we’ve typed ‘TRUE’ as our three conditions.  As all of these are TRUE, the return for the function is as you would expect – true!  Easy.

Now, let’s mix things up a little:-

=AND(TRUE,FALSE,TRUE)
=FALSE

If one of the logical tests returns FALSE then the whole function falls over and returns a FALSE value.  Getting it so far?  You might be thinking that these aren’t tests as such, I’m just showing you what happens when I type TRUE or FALSE in as conditions, as these are the two possible results that will come out of your tests.  Let’s look at a more real-world example.

Let’s say we wanted the function to check whether three seperate values are less than 5.  In the screenshot below we can see the function at work:-


Here we have used it to test if the values in cells A2, A3 and A4 are less than 5.  Pressing the enter key will give us:-


If you break the function down based on the outcomes of your conditional tests, it would look like this:-

=AND(A2<5, A3<5, A4<5)
=AND(TRUE, TRUE, TRUE)
=TRUE

Still making sense?  You can use any kinds of logical test within this function to see if a set of values evaluate to true and then, if you so wish do something with them.  For example you might want to construct an IF function which looks at multiple values and does one thing if the AND function evaluates to TRUE, and another if FALSE:-

=IF(AND(A1<5, A2<5, A3<5),”All less than 5″,”Not all less than 5″)

It really is that easy, and with a bit of imagination can be quite a powerful tool.  Used in conjunction with other logical functions (for example OR), you can really build some exciting decision-making functionality into your otherwise flat worksheets.

Leave a Reply

Your email address will not be published. Required fields are marked *