Oh, have times changed. When I was a kid, my parents constantly told me, “no ifs or buts”. Clearly, Microsoft Excel wasn’t around at that time or I could’ve provided a better argument for using these words. Excel’s “IF” function is a useful formula. I’ll show two different examples of how they could’ve used this feature to determine my allowance. (Includes Excel worksheet with examples.)

## What is Excel’s IF Function?

The **IF function** is one of simplest and most useful spreadsheet functions. It can fill cell fields for you based on evaluating a condition. The wizard-like function requires you to fill 3 Function Arguments or data elements:

### Excel 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 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 things like 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 tasks needed to get an allowance. If the task was completed, (TRUE situation) a value would be applied toward the allowance. If the task 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 in cell C2.

**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.

*To enter your IF Function Arguments,*

- Click the spreadsheet cell where you wish to use the formula
- From the
**Formulas**tab, click**Insert funtion…** - In the
**Insert Function**dialog textbox, type “**if**” - Make sure your cursor is in the
**Logical_test**textbox - Click the spreadsheet cell you wish to evaluate. Excel will fill in the cell address such as “B2”
- Append 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.

## 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 fought for pay on partial tasks. 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.

The new formula is basing the allowance off the % Done column. If the task completion number is greater than .5, a prorated amount was applied to the allowance. If the task completion rate was .5 or below, a negative amount was applied to the allowance. Loosely translated, a “half-assed” performance cost money.

Excel’s IF function is a versatile and useful function. Once you get the hang of it, you’ll start using it in more complex scenarios. The two examples presented here were foundational. But you can use IF functions to handle more complex transactions such as applying sales tax, shipping charges 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.