Using IF Statements and Nested IF's

Question: How do you create an IF statement, and can you use more than one IF?

IF statements are one of the most widely used formula's within Excel. It can be broken down into separate sections:

=IF(B2>10,"Yes","No")

* An additional thing to remember is the comma's that are inside the IF statement. These comma's are used to separate the different elements of the statement.

Nested IF Statements:

A Nested IF Statement is an IF Statement within an IF Statement. So if you have more than one condition to be applied to a cell, you can create an IF statement to provide you with an answer. For example (using cell B2 as the target):

You want a formula that tells you if the value of a cell is less then 10 and displays the text 'Under 10', or if the value of the cell is equal to 20 and display the text 'Exactly 20!', or if the value of the cell is greater than 30 and exter the text 'Over 30'. If none of these conditions are met, then response with the text 'Please Try Again'.

=IF(B2<10,"Under 10",(IF(B2=20,"Exactly 20!",(IF(B2>30,"Over 30","Please Try Again")))))

• The 1st and 2nd statements are written without a false condition.
• The 3rd statement is the same as a standard IF statement.
• There are 5 closing brackets ) at the end of the nested IF statement to match the total number of open brackets.

* Be aware that you are limited to a maximum of 8 statements within a nested IF statement.

Using AND / OR within a nested IF statement:

You can enhance your nested IF statement by using AND or OR. This allows you to further develop your question to encompass a wider range within the condition.

The image below shows a sales chart for four districts over 6 months. The target total over these 6 months is 2,500. Your manager has asked for a formula that will tell him if:

• Both salesmen from a district have achieved sales over target
• One salesman or the other have achieved sales over target
• Neither salesman have have achieved the sales target

The formula used to achieve the above conditions is as follows:

=IF(AND(J4<2500,J5<2500),"Both Under",(IF(AND(J4>=2500,J5>=2500),"Both Over",(IF(OR(J4>=2500,J5>=2500),"One Over")))))

• The 1st checks that both J4 AND J5 are less than 2500
• The 2nd checks that both J4 AND J5 are greater than or equal to 2500
• The 3rd checks that either J4 OR J5 are greater than or equal to 2500