Creating Conditional Tests


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 507.

You might also be able to satisfy some of your conditional curiosities by using the conditional formatting feature in Excel. For details, see "Formatting Conditionally" on page 284.

image from book 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 12-4 lists the six logical operators.

Table 12-4: Logical Operators
Open table as spreadsheet

Operator

Definition

=

Equal to

>

Greater than

<

Less than

> =

Greater than or equal to

< =

Less than or equal to

< >

Not equal to

The result of a conditional test is either the logical value TRUE (1) or the logical value FAESE (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 seam-lessly 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 12-39, then the respective wizards are installed.

image from book
Figure 12-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 12-40.

image from book
Figure 12-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.

Note 

The buttons you use to launch all but two of the Excel add-ins appear in the Solutions group on the Formulas tab; buttons for the Analysis Toolpak and the Solver add-in both appear in the Analysis group on the Data tab. For more information about the Analysis Toolpak, see Chapter 13, "Using Functions"; for more information about the Solver add-in, see Chapter 18, "Performing What-lf Analysis."

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 507.

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 12-41.

    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 12-42 appears.

  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.

On the CD You'll find the image from book Pacific Sales Transactions.xlsx file in the Sample Files section of the companion CD.

  1. 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.

  2. 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.

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

  4. 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.

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

image from book
Figure 12-41: The Conditional Sum Wizard helps you construct SUM formulas that are choosy about what they include.

image from book
Figure 12-42: These wizard pages let you select the cells to include in your calculation.

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 12-43.

image from book
Figure 12-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 12-43 is enclosed in braces, indicating an array formula. For more information about arrays, see "Using Arrays" on page 468.

Inside Out-Watch Out for Spaces

image from book

The Conditional Sum Wizard isn't smart about space characters. For example, if a label in the column of criteria includes an invisible space character at the end of the text string, Excel excludes it from the total, even if all the instances are otherwise identical.

image from book

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 512. 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 12-44.

    Note 

    If the Lookup button does not appear on the Formulas tab, you need to install the add-in. See "Using the Conditional Sum and Lookup Wizards" on page 478.

  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 12-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 12-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.

  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.

image from book
Figure 12-44: Specify the lookup range and the row and column you want to find using the first two steps of the Lookup Wizard.

image from book
Figure 12-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.

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

image from book
Figure 12-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 12-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.



Microsoft Office Excel 2007 Inside Out
MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))
ISBN: 073562321X
EAN: 2147483647
Year: 2004
Pages: 260

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net