Page 82 - Excel Data Analysis

05 537547 Ch04.qxd 3/4/03 11:52 AM Page 68

EXCEL DATA ANALYSIS

CREATE A CONDITIONAL FORMULA

f you only want Excel to perform a calculation when a combination of cell references, operators, constants, or even
cell contains a specific value, you can create a other functions to create the arguments. For example, you
I conditional formula. A conditional formula returns a one can use the SUM function to determine if the total of a series
value if the condition is True, and a different value if the of cells is greater than 450 by typing SUM(A1:A5)>450.
condition is False. For example, an analyst may ask Excel For the value_if_true argument, you specify the value
to calculate the net sales on an item only after a certain that the formula should return if the logical_test is True.
number have sold, or the Shipping department may create a You can also define an argument for the value_if_false
formula that calculates a different shipping cost depending argument if you want the function to return a value when the
upon the price of the item.
logical_test argument is False. With both of these
You create a conditional formula using the IF function. arguments, if you want to return a text string value, you must
There are three different arguments for the IF function, but place the return string in quotation marks. For example, the
only the first two are required: following formula places the value True in the formula cell if
the value of A1 is larger than 100, and False if the value of
=IF(logical_test, value_if_true, A1 is less than or equal to 100:
value_if_false)
=IF(A1>100, "TRUE", "FALSE")
For the logical_test argument, you must specify an
argument that returns a logical value of either True or Excel also has a way to build conditional formulas with the
False. For example, you can see if a cell value is greater convenience of a Wizard. For more on the Conditional Sum
than 125 by typing A1>125 as the argument. You can use any Wizard, see Chapter 11.
CREATE A CONDITIONAL FORMULA

⁄ Click the cell for the ‹ Type the condition you › Type the value if the
formula. want to check. condition is True.
¤ In the Formula bar, type ■ As you type, the syntax for
=IF(. the function displays under
the Formula bar.

68