Microsoft Excel 2010

The **IF** function is one of the most
powerful functions available within Excel. It will not only perform calculations
but it will also look at the criteria and make decisions as to which
formula to apply.

For example, let’s say you have a sales team of 1500 people. You have a commission
plan that pays 10% to sales staff if their sales reach $200,000 in a year.
Without the power of the **IF** function, you would have to determine whether
or not to apply the formula to each employee by considering each employee’s
sales. The **IF** function takes the decision-making task away from you
and places it back in your formula where it belongs.

To best illustrate this command, consider the following data:

In the Commission column (starting in cell F2), you want to add an IF command that calculates IF the sales in E2 are greater than $200,000 (variable located in cell B3) then pay 10% (variable located in cell B4). If not,then pay 5% (variable located in cell B5).

You don't have to document your variables outside of your formula (as shown above in columns A and B); but it is highly recommended. Refer to the article Referencing Formulas in Variables for more information.

**To create the IF command for the above example: **

- Select cell
**F2**. - Click on the button at the left of the Formula Bar.
- Select the
**IF**command from the list then**OK**. The following window appears: **Logical test**: This means what condition are you looking for. In this example we're looking to see if the sales are over $200,000; hence, this field should show as E2>B3. However, since you need the reference to stay as B3 when you copy the formula down for the other employees, you need to make B3 and absolute or named reference. For the purpose of this article, we will use an absolute reference (E2>$B$3). The easy way to insert this formula:

- Click in the
**Logical test**field. - Click in cell
**E2**(the address is inserted in the Logical test field) - Type
**>** - Click in cell
**B3**then press the**F4**key once. B3 should now show as $B$3.

- Click in the
**Value if true**: This means what value or formula do you want Excel to insert if the logical test is true, i.e. if the sales ARE greater than $200,000. You could enter a value, such as 100 for a hundred dollar bonus or text within quotes "Good effort" if you wanted Excel to return text. In this example, if the sales are greater than $200,000 we want to multiple the sales by 10%, which is located in cell B4. Again, you will need an absolute reference to B4 so that you can copy it to the other employees. To do this:- Click in the
**Value_if_true**field. - Click in cell
**E2**(the address is inserted in the Logical test field) - Type
***** - Click in cell
**B4**then press the**F4**key once. B3 should now show as $B$4. **Value if false:**This means what value or formula do you want Excel to insert if the logical test is false, i.e. if the sales ARE NOT greater than $200,000. Again you could enter a value or text within quotes. In this example, if the sales are not greater than $200,000 we want to multiple the sales by 5%, which is located in cell B5. Again, you will need an absolute reference to B5 so that you can copy it to the other employees. To do this:- Click in the
**Value_if_false**field. - Click in cell
**E2**(the address is inserted in the Logical test field) - Type
***** - Click in cell
**B5**then press the**F4**key once. B3 should not show as $B$4. - Now your window looks like this:
- Choose
**OK**to finish. - Copy the formula down column F to apply the formula to the other employees.

If you don't see IF in the list, choose **All**
for a category or use the search box. If this still doesn't help, refer to
the Using Formula Functions article (Inserting
Functions section).

Notes

- If you wish your IF function to continue with more conditions and actions, refer to the Nested If Function article.
- Microsoft Excel provides additional functions that can be used to analyze
your data based on a condition. For example, to count the number of occurrences
of a string of text or a number within a range of cells, use the
**COUNTIF**worksheet function. To calculate a sum based on a string of text or a number within a range, use the**SUMIF**worksheet function. These and all other formula commands are located in the Insert Function Wizard.

- Formulas: About (A MUST-READ!)
- Absolute Cell References – to lock a reference to a cell
- AutoSum – to add up (sum) a series of numbers
- Date Functions – to properly reference calendar dates
- Formula Functions – Average, Minimum, Maximum, Count, and more!
- If Function looks at a cell reference, compares it to specified criteria and performs a specified action
- INT Function - rounds a number down to the nearest integer
- Lookup Function: Vector – to lookup variables from a list and return a variable
- Calculation Options – switch to manual calculation mode to improve data entry response
- Nested If Function – same as IF Function but able to take multiple actions based on multiple conditions
- PMT Function - calculates the payment for a loan based on constant payments and a constant interest rate
- Round Function –rounds the value of a number to a specified number of digits
- Status Bar - be sure to check this one!