2007 MicrosoftВ® Office System Inside Out (Bpg-Inside Out)

A conditional test formula compares two numbers, functions, formulas, labels, or logical values. You can use conditional tests to flag values that fall outside a given threshold, for example. You can use simple mathematical and logical operators to construct logical formulas, or you can use an assortment of built-in functions. For information about using conditional test functions, see “Understanding Logical Functions” on page 471.

Each of the following formulas performs a rudimentary conditional test:

=A1>A2 =5−3<5*2 =AVERAGE(B1:B6)=SUM(6,7,8) =C2="Female" =COUNT(A1:A10)=COUNT(B1:B10) =LEN(A1)=10

Every conditional test must include at least one logical operator, which defines the relationship between elements of the conditional test. For example, in the conditional test A1>A2, the greater than (>) logical operator compares the values in cells A1 and A2. Table 13–4 lists the six logical operators.

The result of a conditional test is either the logical value TRUE (1) or the logical value FALSE (0). For example, the conditional test =A1=10 returns TRUE if the value in A1 equals 10 or FALSE if A1 contains any other value.

Using the Conditional Sum and Lookup Wizards

Excel includes two useful tools called wizards that help you assemble frequently used yet confusing types of formulas. The Conditional Sum Wizard and the Lookup Wizard are provided as add-ins, which are special types of macros designed to integrate seamlessly into Excel. To see whether you have these wizards installed, look at the Formulas tab. If you see the Conditional Sum or Lookup buttons, as shown in Figure 13–39, then the respective wizards are installed.

Figure 13–39: The Conditional Sum and Lookup buttons live on the Formulas tab when installed

If you don’t see buttons for either add-in, click the Microsoft Office Button, Excel Options, and then click the Add-Ins category. In the Manage drop-down list at the bottom of the dialog box, select Excel Add-Ins, and then click the Go button to display the Add-Ins dialog box shown in Figure 13–40.

Figure 13–40: Use the Add-Ins dialog box to install additional tools.

In the Add-Ins dialog box, select the check boxes for both the Conditional Sum Wizard and the Lookup Wizard (and any others you want), and then click OK to install them. Excel will prompt you for permission to proceed-more than once, if you selected more than one add-in to install.

Creating Conditional Sum Formulas

The Conditional Sum Wizard creates formulas using the SUM and IF functions. This wizard not only makes constructing these formulas easier and faster but also shows you how these formulas are constructed so you can build your own conditional formulas without the wizard.

For more information about the IF function, see “Understanding Logical Functions” on page 471.

To build a conditional formula, follow these steps:

1. Select the table or list containing the values you want to use, and click the Conditional Sum button on the Formulas tab to display the wizard page shown in Figure 13–41.

   Figure 13–41: The Conditional Sum Wizard helps you construct SUM formulas that are choosy about what they include.

   If you click anywhere in the table before you start the wizard, Excel automatically selects the current region for you. If Excel selects the correct region, click Next. Otherwise, drag to select the range you want to use. Remember to include the row and column labels. After clicking Next, the page shown on the left in Figure 13–42 appears.

   Figure 13–42: These wizard pages let you select the cells to include in your calculation.

2. In the Column To Sum list, select the name of the column from which you want to extract totals.

   This is why you need to select the labels in Step 1 of the wizard. If the column labels do not appear in the list, click Back, and reselect the range.

You’ll find the  Pacific Sales Transactions.xlsx file in the Sample Files section of the companion CD.

3. Still in Step 2 of the wizard, specify the condition to use when selecting the values you want to include in the total. In the Column list, select the name of the column containing the labels you want to conditionally check, select an operator in the Is list, and then select a value in the This Value list.

   The contents of the This Value list change depending on the column selected in the Column list. The This Value list displays only the unique values in the selected column, ignoring duplicates.

4. Click Add Condition.

   The criteria you specify are added to the list at the bottom of the page. You can add as many as seven conditions. If you change your mind about any condition, select the condition from the list, and click Remove Condition. When you have finished editing conditions, click Next.

5. In Step 3 of the wizard, either choose Copy Just The Formula To A Single Cell or choose Copy The Formula And Conditional Values.

6. Click Next, and then select the cell where you want to place the resulting formula. Or, if you chose the Copy The Formula And Conditional Values option in Step 3 of the wizard, the wizard adds an intervening step, letting you first select the cell where you want the conditional value to go.

7. Click Finish. Excel pastes the resulting formula (and the optional conditional value) in the worksheet in the locations specified.

You can add more conditional formulas, or if you already have a list of unique values you can use for comparison (such as salesperson names), you can copy the formula as needed (but only if you used the Copy The Formula And Conditional Values option in Step 3 of the wizard), as shown in Figure 13–43.

Figure 13–43: We added a list of unique salesperson names (conditions) in column E and copied the conditional sum formula to cells F5:F9.

The resulting formula shown in the formula bar in Figure 13–43 is enclosed in braces, indicating an array formula. For more information about arrays, see “Using Arrays” on page 434.

Creating Lookup Formulas

The Lookup Wizard creates formulas using the INDEX and MATCH functions. Like the Conditional Sum Wizard, it makes constructing lookup formulas easier and faster, and it also illustrates how these formulas are constructed so you can build them yourself later. For more information about the INDEX and MATCH functions, see “Understanding Lookup and Reference Functions” on page 476. To build a lookup formula, follow these steps:

1. Click the Lookup button on the Formulas tab to display the wizard page shown on the left in Figure 13–44.

   Figure 13–44: Specify the lookup range and the row and column you want to find using the first two steps of the Lookup Wizard.

   Note

   If the Lookup button does not appear on the Formulas tab, you need to install the addin. See “Using the Conditional Sum and Lookup Wizards” on page 444.

2. Select the table or list containing the values you want to use. If you click anywhere in the table before you start the wizard, Excel automatically selects the current region for you. If Excel selects the correct region, click Next; otherwise drag to select the range you want to use. Remember to include the row and column labels.

3. Click Next. The page shown on the right in Figure 13–44 appears.

4. Select the name of the column containing the value you want from the Select The Column Label drop-down list. (This is why you need to select the labels in Step 1 of the wizard.) If the labels don’t appear in the list, click the Back button, and reselect the range.

5. Click Next, and then decide whether you want the lookup parameters as well as the result to be inserted in your worksheet, as shown in Figure 13–45. We recommend inserting the parameters (conditions), as we will show later. Select the Copy The Formula And Lookup Parameters option, and then click Next.

   Figure 13–45: If you copy the formula to a single cell, the parameters are fixed; copying both the formula and the parameters lets you create a lookup table.

6. Select the cell where you want the resulting formula to be placed. If you chose the Copy The Formula And Lookup Parameters option in Step 3 of the wizard, the wizard adds two extra steps. If you did this, select the cell where you want the first parameter to go, click Next, and click a cell for the second parameter. Then click Next, and click the cell where you want the conditional formula to go.

7. Click Finish.

Figure 13–46 shows an example of how you can use the Lookup Wizard to build a lookup table.

Figure 13–46: You can enter different months and product numbers to change the corresponding value in cell R5.

As mentioned previously, when you select the Copy The Formula And Lookup Parameters option in Step 3 of the Lookup Wizard, Excels inserts the parameters in your worksheet; in our example, we specified cells P5 and Q5. The resulting lookup formula (in cell R5) refers to these inserted values using relative references. As you can see in the formula bar in Figure 13–46, the first arguments for the MATCH functions are relative references to our specified cells. Using relative references in this way, you can perform two tasks. First, you can type other valid parameters (Sept, Product 12, or both, for example) in the parameter cells (P5 and Q5), and the lookup formula finds the corresponding value at the new intersection. Second, because the parameter references are relative, you can copy the formula to additional cells and type additional parameters into cells in the same relative locations.