Functions that make choices

functions that make choices

In Excel, there are several functions that are used to make choices for you while you work. One such choice might be based on a test condition. For example, you can build a formula in Excel that says, 'If a certain test condition is true, do this. If it is not true (else), do that.' These functions can also make decisions that render a 'Yes or No' response. In this section, we'll look at the IF, nested IF and IS functions.

IS functions are a special set of functions that, based on the condition being tested, render a value of true or false. They are often used in conjunction with the IF function to gracefully handle worksheet errors.

Although it's a stretch of the imagination, even the SUM function is smart to the degree that it can reject text values when they are encountered in a sequence of numbers. Excel even features a SUMIF function that conditionally sums a range of values.

Other functions that make choices include HLOOKUP and VLOOKUP, which will, based on a given test value, look up values from a table of values. As its name implies, the CHOOSE function selects items from a list of values. In the next lesson, we will return to this topic and you will see how to use HLOOKUP and VLOOKUP. Here we will start with the IF function.

the IF function

New Excel users frequently overlook the IF function. When used alone, the IF function tests for the existence of specific conditions within a cell. If the test yields a 'true' result, the function will then carry out a specific action. If the cell test result is 'false,' the function will execute an alternate action. IF takes the following form:

Suppose you are in the mail order business, and you must charge sales tax on the items you sell. The sales tax rate is based on the state in which your customer lives. Let's also assume your business only sells to customers in California and Arizona. The tax rate in California is 8.25 percent and the tax rate in Arizona is 6.0 percent. Therefore, we establish the following IF statement: if the 'state' equals California, charge 8.25 percent, otherwise, charge 6.0 percent. Your Excel worksheet might look like Figure 1-1.

Figure 1-1: A sample Excel worksheet

Note that column D is where you'll need decision-making logic. Also note that in column E, I've created a simple formula to add the contents of cells C2 and D2, which we intend to copy down the rest of the column. If you like, create this worksheet on your own computer now or download the Excel workbook for this lesson and use Sheet 1 to follow along.

The IF function we are building in cell D2 will need to look at what's in A2 and then, depending on what it finds, multiply by either 0.0825 or 0.06. The complete IF function should look like this:

Type this into cell D2 of your own worksheet and verify that the result is the correct one. When you are finished, copy the IF function and the formula down the column to add the amounts of the sale and the appropriate sales tax. Format column D to currency.

The result looks like the example below:

Figure 1-2: The completed worksheet

Now, if you change any of the AZs to CAs, you'll see the new tax rate applied to the value in the Amount column. What happens if you change either a CA or an AZ to WA? If you tried it on your worksheet, you found Excel applied the tax rate of 6 percent. In other words, if the IF statement finds a state abbreviation other than CA, it will apply 6 percent to the amount in the Amount column. Consequently, as you begin doing business in other states, where there may be sales tax rates such as 7.25 percent, 6.5 percent, and so on, you will need to add state-specific data to the IF statement, or find another function to handle these multiple and varied choices.

use of quotes

Note also that we typed 'CA', putting it in quotes. When using IF and other functions, the text field must be in quotes.