Excel – Logical Functions

Logic

Logical functions are probably one of the most commonly used in Excel. You’d be hard pressed to find any semi-decent spreadsheet without an IF statement in it somewhere.

 

If you have never used a logical function or argument before, it’s basically one that has two possible outcomes – true or false or in computer speak 1 or 0.

 

In this blog I am going to show you how to use the following logical functions;

 

  • IF
  • AND
  • OR
  • XOR (2013)
  • IFERROR
  • IFNA

 

IF

 

This is the main one. It can be a simple TRUE/FALSE statement, or can be nested several times to perform multiple sequential tests, each one giving a TRUE/FALSE outcome. The basic syntax for an IF statement is:

 

=IF(logical test, do if true, do if false)

 

Examples of logical tests might be;

 

A5 = 100

 

D4>=25

 

G23=F23

 

E10 = “Excelmate”

 

If the test is TRUE then the “do if true” part of the statement is executed. This could be a calculation, a look up, display a text string…pretty much anything you can create using functions or adding text. If the outcome to the test is FALSE, then it will execute the “do if false” bit of the statement. Again, you decide what happens here. Note that the FALSE bit is optional, so you don’t need to enter anything here. However, if the outcome is FALSE, and you have opted to leave this blank in your formula, Excel will display a not particularly helpful FALSE in your cell. If you want to display an empty cell simply put “” (no space between the speech marks) in the FALSE part.

 

A few basic examples;

Basic IF statement

Basic IF statement

A basic IF statement is fine and dandy, but you will probably want to perform a combination of tests. This is where AND and OR come in handy.

AND & OR

More often than not, you will find these nested in an IF statement. You can use them on their own to get a TRUE or FALSE outcome, but personally I have not found any real use for them in that way.

Both AND & OR can each perform up to 255 tests;

=AND(test1, test2, test3,…test255)

=OR(test1, test2, test3,…test255)

In order for AND to return TRUE, ALL tests must be TRUE. If just one is FALSE then the whole statement becomes FALSE.

In the case of OR, as long as one test is TRUE, the whole statement returns a TRUE value. To return a FALSE result ALL tests must be FALSE.

A common mistake I see is people mix up AND & OR. In their heads they think AND when logically they mean OR and vice-versa. You will soon see if you get some odd results when you run your formula!

What is initially illogical is how to nest AND & OR within an IF statement;

=IF(AND(test1,test2,test3),do if true, do if false)

=IF(OR(test1,test2,test3),do if true, do if false)

In your head you are probably saying “if test1 = x, and test2 = y, and…” which tempts you in to writing it as you think it…but don’t.

Here’s a few examples to see what they give:

Combining IF and AND

Combining IF and AND

Or if using OR…

Combining IF and OR

Combining IF and OR

IFERROR & IFNA

In earlier versions of Excel if you wanted to check if a function returned an error you had to nest an IS function (ISERROR, ISNA, ISREF etc.) within and IF statement. As these are such common tests they have introduced these two functions that combine the logical IF and the logical IS type function. These are useful for example, to keep your cells tidy rather than getting a load of error messages all over the place.

A few examples for you;

Using IFERROR

Using IFERROR

In this example, the formula does a VLOOKUP and if it returns and error, it will display nothing rather than the usual #N/A error message. I could have used IFNA in this case as the VLOOKUP returns that type of error bur IFERROR is a good catch all for all types of errors.

XOR

This has been around in other programming languages for years but never featured in Excel until they brought out 2013.

If you have never come across this function before it is a little unusual. Whereas AND returns TRUE if ALL tests are true, and OR will return TRUE if any one or more tests are found to be true, XOR (eXclusive OR…get it now?) will behave in the following way;

2 logical tests: if A is TRUE and B is TRUE then result = FALSE

If A is TRUE and B is FALSE then result = TRUE

If both A and B are FALSE then result = FALSE

However…if there are 3 or more tests, then things get interesting;

TRUE – TRUE – TRUE = TRUE

TRUE – TRUE – FALSE = FALSE

TRUE – FALSE – TRUE = FALSE

FALSE – TRUE – TRUE = FALSE

FALSE – FALSE – TRUE = TRUE

FALSE – FALSE – FALSE = FALSE

Have you worked it out yet?

In the case of 2 logical tests, the XOR function basically returns TRUE if the result of EITHER test is TRUE, but not both and not neither i.e. both are FALSE.

In the case of three or more logical tests, XOR returns TRUE if the number of TRUEs is ODD, and will return FALSE if the number is even. Obvious!

Personally I have never had a need for such a function but perhaps one day it might come in handy. If you find a good way of using it then fell free to post a comment on this site.

So there you have some logical functions. As with all functions in Excel, you really get to see their real power by nesting functions together. The IF statement alone can be nested up to 64 times in a single statement. Having said that there is probably something seriously wrong with your spreadsheet if you need to create something that long-winded, and should probably be looking to use VBA to do the work. Putting that aside however, logical functions are probably one of the most commonly used functions in Excel after autosum and are an essential part of your spreadsheet functions arsenal.

 

 

Advertisements

Posted on June 5, 2014, in Functions & Formulas and tagged , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: