The IF function is useful for making quick decisions or performing comparisons in Microsoft Excel. It’s an easy but versatile logical function for handling many scenarios, ranging from financial models to assigning allowances. (Tutorial includes a worksheet with Excel IF formula examples.)

## What is Excel’s IF Function?

The **IF function** is one of the simplest and most useful Excel logic functions. It can fill cell items for you based on evaluating a condition such as a cell’s content and logical operators. What’s appealing about the function is it can be used with other functions to create more complex formulas. You might think of it as a formula building block and you can find it in the **Logical** category.

The wizard-like dialog allows you to fill 3 **Function Arguments** or data elements, but you could use the formula bar once you master it. This is the easiest way to learn an Excel formula because you can see if it returns your expected result. For example, at the bottom left of the dialog, a line reads “**Formula result =**.”

### The IF Function Arguments

Field | Definition |
---|---|

Logical_test | A test on a cell value that is either TRUE or FALSE. |

Value_if_true | The value Excel will put in a cell if the test is true. |

Value_if_false | The value Excel will put in a cell if the test fails. |

Despite not having Microsoft Excel, my parents routinely employed this type of IF logic when calculating my allowance. Their version read:

**IF** you *empty the garbage* **AND** *mow the lawn* **AND** *wash the dishes* **AND** *walk the dog*, you **get your full allowance**.

And since I grew up in New England, this logic would change with the seasons to account for leaves and snow.

## Setting Up the IF Function

Although Excel can’t issue an allowance, it can calculate the amount using a **logic test** based on whether a cell met a formula condition.

For example, I could create a spreadsheet with the chores needed to get an allowance. If the chores were completed (TRUE situation), a value would be applied toward the allowance. If the chore wasn’t completed (FALSE situation), nothing would be added.

These examples are noted by labels [1] and [2] in the screen snap below.

Using the example above, you might express the logic in the following way:

**IF** cell **B2 equals “Y”**, then use the **Rate **value from cell **C2** ($3.00) in **D2**.

**IF** cell **B2 does not equal “Y”**, then place 0 in cell **D2**.

As you can see in this example, the IF logical condition is either **TRUE **or **FALSE**. And it pays to take out the garbage.

## Comparison Operators

To help evaluate conditions, Excel uses a list of common operators. You probably know these as we probably used them in math class. These operators will evaluate to a logical “true” or “false”. In the table below, B2 and C2 in the Example column are cell references.

Operator | Example |

= (equals) | B2 = “YES” |

< (less than) | B2 < 12 |

> (greater than) | B2 > 112 |

<= (less than or equal to) | B2 <= 12 |

>= (greater than or equal to) | B2 >= 12 |

<> (not equal to) | B2 <> C2 |

*To enter your IF Function Arguments,*

- Click the spreadsheet cell where you wish to use the Excel formula.
- From the
**Formulas**tab, click**Insert function…** - In the
**Insert Function dialog**text box, type “**if**“.

**Note**: On Office 365, there is now a **Logical **button on the **Formulas **tab. You can select **IF** from the drop-down menu.

- Make sure your cursor is in the
**Logical_test text box**. - Click the spreadsheet cell you wish to evaluate. Excel will fill in the cell reference such as “B2”.
- Add the equals sign
**=**and your desired value**in quotes**. For example =”Y”. - In the
**Value_if_true**field, type the value you would like entered in your cell if B2 equals “Y”. In our example, I’ll click cell C3. - In the
**Value_if_false**: field, enter the value the cell should have if B2 does not have a “Y”. I’ll enter 0. I could leave it blank, but the cell would show “FALSE”. - Review the dialog to see if the
**Formula result=**value (label [1] below) is what you expect. If not, check to see if any errors show to the right of the fields (label [2] below).

- Click
**OK**. - Copy the formula to the other cells in your column.

✪** Tip**: Even though the **Value_if_false** field is optional, it’s best to provide a value. Otherwise, Excel will use FALSE in the cell value.

## Excel IF Example 2

The above spreadsheet might have been Version 1 for my parents. A new incentive program would appear based on some parent/child negotiations and competitive neighborhood rates. I probably would’ve requested pay for partial chores. No doubt, my parents would counter with a penalty clause if something was less than half done.

Excel is flexible when it comes to IF statements and can evaluate more than a simple “Y” or “N.” For example, if we convert our previous **Done?** column to a **% Done** column with a number, we can accommodate these new requirements such as:

`=IF(B2>0.5,B2*C2,-C2)`

The new formula returns the allowance based on the % Done in Column B. If the chore completion number is greater than .5, a prorated amount was applied to the allowance.

If the chore completion rate was .5 or below, a negative amount was applied to the allowance. Loosely translated, a “half-assed” performance costs money. You could also apply colors using conditional formatting.

## Troubleshooting Tips

There are a couple of reasons why the IF function doesn’t work as expected.

**You didn’t have quotes around a text string**. For example, you used B2=Y instead of B2=”Y”.**The quotes aren’t needed if you use the value TRUE or FALSE as Excel recognizes them.****You have a data type mismatch**. For example, your comparison operator was comparing a number in one cell such as “twelve”, but the comparison cell had 12. However, Excel is forgiving if you’re evaluating number formats such as 1.5 versus $1.50.

As you’ve seen, this is a versatile and useful function. Once you get the hang of how to use IF function in Excel, you’ll start using it in more scenarios. The two examples presented here were foundational. But you can use IF functions to handle other transactions such as applying sales tax, stock values, shipping charges, fixing Excel DIV 0 errors, or even nested IF functions with Boolean logic. And if you have kids, let them build the Excel spreadsheet and give them a bonus for using the IF function.