This Excel Tutorial demonstrates how to use the Excel IF Function in Excel to create If Then Statements, with formula examples.

IF Function Description:

The IF Function Checks whether a condition is met. If TRUE do one thing, if FALSE do another.

Formula Examples:

Example	Formula	Result
Condition TRUE	=IF(C5>5,D5,E5)	Greater Than 5
Condition FALSE	=IF(C6>5,D6,E6)	Less Than 5
Text Conditions Met	=IF(C7=”apples”,D7,E7)	match
Text <> Equal	=IF(C8<>”pears”,D8,E8)	match
And	=IF(AND(C9>5,C9<10),D9,E9)	In Range
Or	=IF(OR(C10<5,C10>10),D10,E10)	Out of Range

Syntax and Arguments:

The Syntax for the IF Formula is:

Function Arguments ( Inputs ):

logical_test – Logical expression. Example: A1 > 4.

value_if_true – Value or Calculation to perform if logical expression is TRUE.

value_if_false – Value or Calculation to perform if logical expression is FALSE.

Additional Notes

Use the IF Function to test if a condition is TRUE. If the condition is TRUE then do one thing. If it’s FALSE then do another. The condition must be a logical expression (ex: a1 >5), a reference to a cell containing TRUE or FALSE, or an array containing all logical values.

The IF Function can only test one condition at a time. However, you can “nest” other logical functions within the IF condition to test multiple conditions at once:
=if(AND(a1>0,a2>0),TRUE,FALSE)
=if(OR(a1>0,a2>0),TRUE,FALSE)
=if(XOR(a1>0,a2>0),TRUE,FALSE)
OR Functions test if one or more conditions are met.
AND Functions test if all conditions are met.
XOR Functions test if one and only one condition are met.

You can also “nest” an IF Function within an IF Function:
=if(a1<0,if(a2<0,"Both","only 1"),"only one")

Now for some concrete examples of how the IF function works in practice:

1. Start a New Work book.

2. In Cell A1 enter the value 10 (and press Enter)

3. Then in Cell B1 enter the following formula:

=IF(A1 > 5,”BIGGER THAN 5″,”LESS THAN 5″)

4. The screen should look like this now:

5. If you’ve entered the formula correctly, you will see the message “Bigger than 5” appear in cell B1.

6. The formula you’ve entered in Cell B1 performs the test “A1> 5” i.e it checks if the value in Cell A1 is greater than 5.  Currently the value in Cell A1 is 10 – so the condition is TRUE and the message “BIGGER THAN 5” appears

7. If we now change the value in cell A1 to 2:

Thtn the message in cell B2 is now “LESS THAN 5” as the condition is FALSE.

8. You can keep changing the value in cell A1 and the message in cell B2 will adjust accordingly.

9. Of course there are situations where the condition could give mischievous results:

•    What happens if we enter the Value 5 in Cell A1 ?

•    What about if we leave Cell A1 blank ?

•    What about if we put some text in Cell A1 e.g  the phrase DOG

More on the Excel IF Function

We will now look at the IF function in more detail. It can be used to analyse large quantities of data very easily.

Imagine that you are a Area Sales Manager and have a Sales Team. You could record the total Sales that each person makes in a simple Excel Spreadsheet:

Suppose that the criteria for a bonus was that the Sales made by that person exceeded £ 40,000. You could just “eye-ball” the data and determine that only Anton , Newton and Monique met the target.

This is quite easy when you’ve got just a handful of names. However if you’ve got several then there is scope for error. Fortunately by using Excel’s IF function it can be done much faster and safer.

Set up a new workbook and type in the data as per above. Then in cell D4 type in the following formula :-

=IF(C4>40000,”BONUS PAYABLE”,”NO BONUS”)

so that you have:

Note how Excel shows the structure of the IF formula – which is a useful aide memoire.

Having typed in the formula press ENTER and you see it evaluate for the first row:

The formula has been evaluated for Martin – as he earned less than £ 40,000 he isn’t entitled to any bonus.

And then we drag the formulae down by clicking the bottom right hand corner and dragging down we can determine whether or each person is entitled to a bonus:

And we see that Excel has determined which of the Sales People are entitled to a bonus.

Return to the List of all Functions in Excel

How to use the IF Function in Excel:

To use the AND Excel Worksheet Function, type the following into a cell:
=AND(
After entering it in the cell, notice how the AND formula inputs appear below the cell:

You will need to enter these inputs into the function. The function inputs are covered in more detail in the next section. However, if you ever need more help with the function, after typing “=IF(” into a cell, without leaving the cell, use the shortcut CTRL + A (A for Arguments) to open the “Insert Function Dialog Box” for detailed instructions:

For more information about the IF Formula visit the
Microsoft Website.

VBA IF Statements

You can also use If Statements in VBA. Click the link to learn more, but here is a simple example:

This code will test if a cell value is negative. If so, it will write “negative” in the next cell.