Build Complex Logic Formulas Using IF, AND, OR

Instructions: If it is easier to work directly in Excel, you can open this in Excel by clicking on the Excel Icon.

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Suppose in the situation represented by the Topic Example Workbook the following pension eligibility conditions apply:

• 1. UK employees are eligible for a pension if they are over the age of 65 or if they are over the age of 60 and earn more than £50,000 a year
• 2. Non-UK employees are eligible for a pension if they are over the age of 60

And we want to build up a formula that determines whether any given employee is eligible for a pension or not. We can build this formula using the AND, OR functions combined with nested IF statements.

We’ll build up the formula in stages, each in a separate column for ease of visibility.

The syntax of an IF statement is =IF(condition, value if true, value if False).

So in the first part we have our condition which is whether a condition evaluates to TRUE or FALSE (e.g. whether a value in a cell is equal to/greater than/less than a value we specify).

The first element of our formula is what we’ll build in Column F which is a formula to determine whether the employee is from the UK or not. Our IF formula then becomes =IF(B2=”UK”,”Y”,”N”), so this returns Y for UK employees and N for non UK employees.

This is where we have an IF statement within an IF statement and is useful for where we have blocks of logic which we want to separate out. In the example above, the logic for UK employees has its own set of logical criteria so can be treated as a separate block of logic contained within its own IF function. The same applies for non UK employees.

If we take the formula above but instead of just having “Y” for UK employees we actually want to have another set of logic criteria for them-i.e another IF statement

In column G we want to have a formula that just assumes that all UK employees over the age of 65 are eligible for a pension: our formula needs to look at the age column (Column C) and return “Y” if the value is greater than 65 and “N” if not.

So the IF statement relating to this logic is =IF(C2>65,”Y”,”N”).

We can now combine this with our original IF statement-if the employee is from the UK we check whether they’re over the age of 65, returning “Y” if they are and “N” if they’re not.

Our formula in Column G then becomes =IF(B2=”UK”,IF(C2>65,”Y”,”N”)). For UK-employees the first condition is met so it proceeds to the second condition of checking whether they’re over the age of 65. For non UK employees this formula will just return FALSE (we haven’t put anything for the ‘Value if False’ element of our first country check but this is fine as the ‘Value if True’ and ‘Value if False’ parameters are optional – if omitted the IF statements just returns TRUE or FALSE).

Now in Column H, instead of returning FALSE for non-UK employees we want to apply another set of criteria to them (check whether they’re over the age of 60) so we need to use another IF statement. This is very similar to what we had for UK employees in our simplified logic: the formula for determining whether the value in the age column is greater than 60 or not is =IF(C2>60,”Y”,”N”).

We can then use this as the ‘Value if FALSE’ element of our original IF statement (evaluating whether the employee is from the UK or not). So if the employee is from the UK then our check for whether they’re 65 kicks in IF(C2>65,”Y”,”N”) and if not we check whether they’re over 60 IF(C2>60,”Y”,”N”)

Our complete formula in Column H is then =IF(B2=”UK”,IF(C2>65,”Y”,”N”), IF(C2>60,”Y”,”N”)) – this will return “Y” for UK employees over the age of 65 and for non-UK employees over the age of 60, and “N” for everyone else.

While Nested IF statements are very useful, there are some situations where another formula is more appropriate. For example if you have multiple categories with thresholds (e.g. ‘Low’ for numbers less than 5, ‘Medium’ between 5 and 10 and ‘High’ for numbers greater than 10), then rather than using a Nested IF statement to return the appropriate category, it is much neater to use an INEXACT VLOOKUP function.

To incorporate our more complicated logic for UK employees we need to use the AND function as well as the OR function. Both have the Same Syntax: =AND(logical 1, logical 2…) and =OR(logical 1, logical 2…). Each of these logical parameters is just a criteria that evaluates to true or false – like the first part of our IF Statement. (so for example whether a value in a cell is equal to/greater than/less than a value we specify).
In this case then we have two separate conditions that we want to evaluate for UK employees to determine whether they’re eligible for a pension:

• Condition 1: Are they over the age of 65?
• OR Condition 2: Are they over the age of 60 AND Receive a salary of greater than £50k?

These elements of our conditions can be represented by using simple criteria formulas (referencing the values in Row 2):

• Over the age of 65: ‘C2>65’
• Over the age of 60: ‘C2>60’
• Salary greater than £50,000: ‘D2>50000’

Each of these will evaluate to TRUE or FALSE – with the AND function all the logical conditions inside it need to evaluate to TRUE in order for the function overall to return TRUE; with the OR function as long as at least one of conditions inside it evaluate to TRUE the function overall will return TRUE.

So our Conditions can now be represented as:

• Condition 1: Are they over the age of 65?: ‘C2>65’
• OR Condition 2: Are they over the age of 60 AND Receive a salary of greater than £50k?: AND(C2>60,D2>50000)

And combining these conditions with an OR function gives us ‘OR(C2>65, AND(C2>60, D2>50000))’.

This function will evaluate to TRUE or FALSE (It will be TRUE if either of our conditions are met) so we can use it as part of our IF Statement reserved for UK Employees:

• So our IF statement block for UK employees becomes IF(OR( C2> 65, AND(C2>60, D2>50000 )),”Y”,”N”)
• And putting this into our completed formula (in cell I2), this becomes: =IF(B2=”UK”,IF(OR(C2>65,AND(C2>60,D2>50000)),”Y”,”N”),IF(C2>60,”Y”,”N”))

In the Topic Example Workbook above build up our ‘Eligible for Pension formula’ in Column I by completing the formulas in the following:

• Column F :A formula to determine whether the country (column B) is equal to “UK” or not (return “Y” if so, otherwise return “N”)
• Column G: If the employee is from the UK, check whether they are over the age of 65 (return “Y” if so, otherwise return “N”). If they’re not from the UK, return FALSE
• Column H: If the employee is from the UK, check whether they are over the age of 65 (return “Y” if so, otherwise return “N”). If they’re not from the UK, check whether they’re over the age of 60 (return “Y” if so, otherwise return “N”).
• Column I: If the employee is from the UK, check whether they are either over the age of 65 or if they are over the age of 60 and receive a salary of greater than £50,000. (return “Y” if either condition is true, otherwise return “N”). If they’re not from the UK, check whether they’re over the age of 60 (return “Y” if so, otherwise return “N”).

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

YouTube Video – Using AND, OR, IF Functions in Excel