IF FUNCTION

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:

Example:

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:

  1. Select cell F2.
  2. Click on the button at the left of the Formula Bar.
  3. Select the IF command from the list then OK. The following window appears:
  4. 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).

  5. 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:

    1. Click in the Logical test field.
    2. Click in cell E2 (the address is inserted in the Logical test field)
    3. Type >
    4. Click in cell B3 then press the F4 key once. B3 should now show as $B$3.
  6. 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:
    1. Click in the Value_if_true field.
    2. Click in cell E2 (the address is inserted in the Logical test field)
    3. Type *
    4. Click in cell B4 then press the F4 key once. B3 should now show as $B$4.
  7. 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:
    1. Click in the Value_if_false field.
    2. Click in cell E2 (the address is inserted in the Logical test field)
    3. Type *
    4. Click in cell B5 then press the F4 key once. B3 should not show as $B$4.
  8. Now your window looks like this:
  9. Choose OK to finish.
  10. Copy the formula down column F to apply the formula to the other employees.

Notes

Additional References