# Excel – Logical Functions

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;

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:

Or if using 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;

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** (e**X**clusive **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 **TRUE**s 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.

Posted on June 5, 2014, in Functions & Formulas and tagged AND, IF, IFERROR, IFNA, logic, logical functions, msexcel, OR, XOR. Bookmark the permalink. Leave a comment.

## Leave a comment

## Comments 0