1.11 IF Statement

The IF statement returns a value in the cell where the formula is inserted. However, the value that is returned depends on something else. For example, in the example below, you will see that a pair of blue pants costs $34.99, but a pair of red pants costs $29.99. When calculating the total owed for 3 red pants and 5 blue pants, it is possible to use a formula in cell D7 that will put the value of $29.99 if the color in cells C7 is red, and $34.99 if the color in that cell is blue. The IF statement in cell D7 shows how this is done.

IF statements use three pieces of information that you will need to add to the formula. First, you put what the condition is. In this case, the criteria that Excel will look for is whether the the value in cell C7 is "Red". The second piece of information in the formula is the value that should be used if the criteria is true, and the third piece of information is the value that should be used if the criteria is false. In this case, if the criteria is false it means that the pants purchased were blue rather than red, since there are only two options of pant color. As such, the first criteria (the value if true) asks Excel to input the cost of red pants and the second criteria (the value if false) asks Excel to input the cost of blue pants.

The second and third parts of this equation (value if true and value if false) do not need to be cell references, like they are in this example. They could instead be fixed values (a number for example), or they could be formulas. For example, if a 15% discount were being given on all purchases at the moment, the following function could be used to input the unit price after the discount: =IF(C7="Red",C2*.85,C3*.85). This would tell Excel to insert the value of $29.99 * .85 (the cost of red pants after the 15% discount) if the color is red, or to put in the value of $34.99 * .85 (the cost of blue pants after the 15% discount) if the color is blue.