## Tutorial 4

Tutorial4.xls

### Conditional Computation

At times the result of a computation depends on whether some condition has been satisfied. For example, our favorite bookstore may sell fiction books at a 20% discount. We want to compute the total price of the purchase of several books. Here is what our calculations should look like:

How can we compute the sale price, if the computation depends on whether the book is a fiction? The following function performs the desired conditional computation for the first row:

`=IF(C7=\$C\$4,B7 - B7*\$D\$4,B7)`

We compare the values of the cell `C7` and the fixed cell `C4` to determine whether this book is a fiction book. The next two arguments for the `IF` function define the two possible actions; the first action is taken if the condition is `TRUE`, the second is taken if the condition is `FALSE`. We can now drag the formula down to compute the sale price for each book:

### Looking up one of several values

We now have a more complicated decision to make. We are selling tickets for a movie theater and there are three kinds of prices: a regular price for adults, and two different discounted prices for seniors and children. We make a table that shows the categories and the prices:

We now want to compute the amount the cashier takes in as the ticket sales are being made. A sample sequence of ticket purchases is shown here, together with the expected outcomes:

We can compute the desired price by a complicated formula that asks two IF questions as follows:

`=IF(B10=\$A\$4,\$B\$4,(IF(B10=\$A\$5,\$B\$5,\$B\$6)))`

to compute the price in the cell `D10`. While this may work fine for just three categories, it becomes terribly cumbersome and confusing when we are faced with several different categories. Instead, we use the `VLOOKUP` function. When you want to insert the `VLOOKUP` function it is the best to go through the Insert menu so that your selection of the function arguments is guided by a dialog box.

We want to look up the ticket kind in the cell `B11` and look up a matching value in the first column of the range of the lookup data `A4:B6`. Once we find the value, we place in the current cell the corresponding value from the column 2 of the lookup data. In our case this is the column that gives us the corresponding prices. If we place this `VLOOKUP` function in the cell `D11` we will produce the desired price for a single Child ticket. However, if we try to drag the formula down to the cells `D12` through `D17` it will not work. We forgot to fix the reference for the lookup data range. Here is the final table:

We now work out a similar problem where the lookup values are numerical values that may not match exactly our lookup table. For example, the instructor wants to assign letter grades to students based on the number of points earned on homeworks and exams. We start by setting up a lookup table:

Because the values we are trying to match are not exact, we omit the last argument to the `VLOOKUP` function. Notice that the dialog explains how this argument works:

to find the closest match in the first column (sorted in ascending order) = TRUE or omitted;

It also reminds us that the values in the first column must be sorted in ascending order. Fortunately, we did this correctly. The value in our cell is compared to the values in the lookup table. To match, our value must be larger than or equal to the lookup value and smaller than the next entry in the lookup table. (For the last category, all values larger than the last value match that category.)

Let us now use this table to assign grades to the following students:

We use the `VLOOKUP` function as shown:

We now want to count how many As, Bs, Cs, and Fs did the students earn. Another function `COUNTIF` helps us in this task. We add a count column to the original lookup table. We compute the first value as

``` =COUNTIF(D10:D24,C7) ```

However, we cannot drag this formula unless we fix the range of data to which we refer. So the correct formula is:

``` =COUNTIF(\$D\$10:\$D\$24,C7) ```

and we get:

Practice these skills and explore the Excel behavior on the following problems:

1. The spreadsheet US-schools.xls contains the spending on public education in US states in the year 2005-2006. Assign label to the states based on their spending on education as follows:
• low: under \$7,500 per child
• medium: between \$7,500 and \$9,000 per child
• good: between \$9,000 and \$11,500 per child
• exceptional: over \$11,500 per child
• Count how many states fall into each category.
• Extend the bookstore example by giving 15% discount for biographies and 10% discount for textbooks. Make a new sheet that has more examples of books (including, of course, examples of textbooks).