The IF function tests a condition and takes an alternative action depending on the result. It is a logical function and gets Excel to think for itself.
For example, if the sales total in cell B4 is higher than £1500 then calculate the commission, and if not then leave the value as it is.
The IF function can only test one condition. Because of this limitation, it is often combined with other functions like AND and OR that can check multiple conditions.
The syntax for the IF function is:
=IF(logical_test, [value_if_true], [value_if_false])
| Argument | Purpose |
|---|---|
| Logical_test | The condition to be tested |
| Value_if_true | The action to be taken if the result is true |
| Value_if_false | The action to be taken if the result is false |
Logical operators
Conditions are tested using logical operators.
| Operator | Action | Example | Result |
|---|---|---|---|
| = | Equals To | 5=5 | True |
| > | Greater Than | 10>5 | True |
| < | Less Than | 10<5 | False |
| >= | Greater Than or Equal To | 10>=8 | False |
| <= | Less Than or Equal To | 10<=10 | True |
| <> | Not Equals To | 10><10 | False |
IF function examples
| Cell B4 | Function | Result |
|---|---|---|
| 1600 | =IF(B4>=1500, “Well done”, “Unlucky”) | Well done |
| 1050 | =IF(B4>=1500, B4*0.1, B4) | 1050 |
| 50 | =IF(B4=200, “Bingo”, 0) | 0 |
| 2000 | =IF(B4>1500, B4-B4*0.05, “No discount”) | 1900 |
Double quotations “” are used to tell Excel to accept the text literally. Any text to be displayed in a cell should be enclosed with double quotations.
When testing a text string, the text will need to match perfectly. By using the SEARCH and ISNUMBER functions you can perform partial text matches and check if the Excel IF function contains text.
