Page 233 - Excel Data Analysis

12 537547 Ch11.qxd 3/4/03 12:48 PM Page 219

USING THE ANALYSIS TOOLPACK ADD-INS 11

Excel creates the conditional sum statement by combining a SUM
function with a nested IF statement. You may find it a little confusing to
interpret the function if you want to modify it. The nested IF
statements become the Number1 argument of the SUM function. This
means that the IF statements return the summed values. For example,
Excel creates the following formula for a list where the conditions are
to sum amounts over $50,000 and State equals Texas:
=SUM(IF($D$2:$D$20="Texas", IF($F$2:$F$20>50000,$F$2:$F$20,0),0))
This example first checks if the value of the record in column D equals
"Texas". If the first condition is True, the second IF statement checks if
the value in column F is greater than $50,000. If both conditions are
True, Excel adds the value in column F to the sum and repeats the
process for the next row in the range. If either value in the row does
not match the condition, Excel adds a value of zero to the sum. See
Chapter 4 for more information on creating conditional IF statements.

■ Excel pastes the formula in £ Click the cell. ■ Excel displays the formula
the specified cell. in the Formula Bar.

219