Chapter 7 - Spreadsheets: Relational & Boolean Operators



1.8 The IF FunctionWith relational operators and Boolean logical functions we have been able to determine if data meets a set of criteria by returning the Boolean Logical values TRUE or FALSE. What if instead of determining if a value is TRUE or FALSE, we wish to type a status message (e.g., “Yes” or “No”) or perform one calculation versus another? The Excel IF function has the ability to logically analyze information, and the flexibility to return answers other than TRUE and FALSE.Simple If functionsThe IF Function syntaxThe logical IF function takes a Boolean value as its first argument. This can either be the result of a Boolean function, relational expression, or a reference to a cell containing a Boolean value. The function tests whether the value is TRUE or FALSE. If the value is TRUE, the computer performs the action specified by its second argument. If the comparison is FALSE the computer performs the action specified by its third argument. The syntax of an IF function is:IF (logical_test, value_if_true, value_ if_ false)The use of the IF function corresponds to the use of the word “if” in everyday English. In the sentence “if it is raining today, I will stay inside; otherwise, I will go outside” the logical test is whether or not it is raining, the value if true is staying inside, and the value if false is going outside. Figure 15715001405890As an example, consider the spreadsheet in Figure 1. The spreadsheet lists the names of several employees, their monthly wage (column C), and their benefit eligibility (column B). Since it is not clear what a TRUE or FALSE value in column B indicates (i.e., does the TRUE in cell C4 mean John is eligible for benefits, or is it TRUE that he is he is ineligible for benefits?), it is desirable to list whether or not an employee is eligible for benefits in a more user-friendly way. One of the simplest examples of the use of the IF function is to take a TRUE or FALSE value and return an associated text label. Assume that the TRUE value in column B indicates that an employee is eligible for benefits. In column D the IF function can be used to display the text “eligible” or “ineligible” based on the value in column B. To use the IF function in cell D4, consider each of the three arguments: the logical test, the value if true, and the value if false.The first argument is the logical test. The logical test is the value that the decision of the IF function is based on. Whatever is placed in this argument must ultimately reduce to a single TRUE or FALSE value. The formula being written needs to determine whether or not John is eligible for benefits. This is indicated by a Boolean value in cell B, so the logical test will be to reference the value in cell B4.The second argument is the value to be displayed if the logical test is evaluated to be TRUE; in this case it will be the text label “eligible”.The third argument is the value to be displayed if the logical test is evaluated to be FALSE; in this case it will be the text label “ineligible”.The resulting formula will then be: =IF(B4,“eligible”,“ineligible”)Notice that the logical test simply references the cell, since the cell already has a TRUE/FALSE value. It is not necessary to write B4=TRUE for this argument. Also note the value_if_true and the value_if_false arguments are enclosed in quotes. This is because the resulting values to be displayed are text. If the quotes are omitted the computer will look for a range named eligible and either returns the value from that range or gives a #NAME! error if the range name does not exist.A Simple example While displaying whether or not an employee is eligible for benefits is nice, frequently translating a Boolean value to a text label is not enough. What if we needed to calculate values, not just display text, based on whether or not certain criteria are met? One example might be calculating an employee’s total compensation including medical benefits, where the company pays the premium for benefit eligible employees. This would require $300 be added to the salary if the employee was eligible for the benefit.To begin to answer this question, consider the simpler problem of determining the benefit value each employee should receive: What formula will be needed in cell D4 that can be copied down the column to determine medical benefit value (cost of premium) for this employee? The benefit value is $300 if the employee is eligible for benefits (as listed in cell B1).To solve this problem, first determine if John receives benefits by looking in the corresponding cell in column B. The TRUE value indicates that he does receive benefits (logical test). Based on the problem statement, if he receives benefits the medical premium is $300 (result if TRUE). If he does not receive benefits there is no premium. i.e., the premium is $0 (result if FALSE).To decide between different results based on some criterion, use an IF function. To better understand the logical structure of this problem, consider writing out the IF function with English text without values inserted (this is called “pseudo code”): If(eligible for benefits, $300, otherwise $0).Translated into Excel syntax this would be: =If(B4,B1,0). Now consider whether each cell reference should be relative or absolute. The formula needed in cell D5 is =IF(B5,B1,0). The value needed for the logical_test changes relatively (B4 to B5). However, the benefit amount remains $300 (as given in cell B1), so this reference should have an absolute row. The value if there is no benefit is just the constant $0, which does not change. The final formula is: =If(B4,B$1,0).To calculate total compensation, the medical premium value can be added to the monthly salary with the formula =C4+E4. A somewhat more complex question would be to calculate this total compensation directly, without first calculating the premium value in a separate cell. To do this, the formula =C4+IF(B4,B$1,0) could be used. Another possible way of writing this formula is =IF(B4,B$1+C4,C4), placing the addition inside of the IF statement for both the values_if_true and the value_if_false arguments.What not to do:What would happen if we instead wrote the formula =If(B4=TRUE,B1,0)? This syntax works equally as well. Frequently however, students try to use quotes around “TRUE” resulting the formula. =If(B4=“TRUE”,B1,0). “TRUE” with quotes is a label, while TRUE without quotes is a value. As with numbers, a label used instead of a value (such as “FALSE” instead of FALSE) can lead to incorrect results. Be careful never to use quotes with Boolean values.Furthermore, the expressions B4=TRUE and B4 are logically equivalent if B4 contains a Boolean value. To see why, consider the possible values of cell B4. If cell B4 contains TRUE, then the result of the relational expression B4=TRUE is true. In this case, the expression B4 is TRUE as well. Now, if B4 contains FALSE, the result of the relational expression B4=TRUE is FALSE. The result of the expression B4 is FALSE in this case as well. In either case (B4 contains TRUE or contains FALSE), the two expressions are equivalent. Therefore it is not necessary to write B4=TRUE.Nesting relational expressions for a logical testLet’s change the original example to the spreadsheet seen in Figure 2. Now assume the employer will pay 50% of the medical premium for employees who qualify for medical benefits and to qualify for benefits, employees must earn at least $1000 per month. Write an Excel formula in cell C4 that can be copied down the column to determine the company’s contribution to the medical premium of each employee. Figure 2148526570485Logically, first determine if John receives benefits by comparing his wage to the $1000 minimum set by the company. Does John receive this $1000 minimum? If John does qualify, calculate the medical premium paid by the company as 50% of $300. If John does not qualify the benefit is $0.To evaluate a condition and decide between different solutions, use the IF function. In pseudo code: If(a person qualifies for benefits by earning more than $1000, then company pays 50% of the premium, otherwise $0) Notice that there are no TRUE/FALSE values already listed on the worksheet that describe whether or not an employee qualified for benefits. This calculation will need to be nested within the IF function’s logical_test. To compare two values in Excel (John’s salary vs. $1000), a relational expression can be used. The relational expression B4>=1000 will evaluate to the TRUE/FALSE value needed for the logical test argument.Also note that the value if true, 50% of $300, is also not listed. Again the expression will need to be nested within the value_if_true argument, in this case B1*B2.Translated into Excel syntax the formula is =IF(B4>=1000,B1*B2,0). Is the formula copied? Yes. To determine if any modifications are needed, consider how each of the values changes when the formula is copied down the column. Does the monthly salary change from B4 to B5? Yes. Does B1 become B2? No, the value of the benefits is fixed. Similarly the value of the percentage paid the company in cell B2 is fixed. Hence the final version of the formula should be =If(B4>=1000,B$1*B$2,0)Nesting boolean logical functions for a logical testWhat if this problem were again changed to require an employee to have a salary between $1000 and $2000 (inclusive) in order to qualify for medical benefits? Changing the criteria will require the logical test (first argument) of the IF function to be modified accordingly.Here both the criteria, that a value is greater than or equal to 1000 and that the value is less than or equal to 2000, need to be true for the statement to be true. Since both criteria must be true for the statement to be true, an AND function will be needed. If solving just for this criterion the following expression could be used: AND(B4>=1000,B4<=2000). Nesting this within the formula results in the following:=If(And(B4>=1000,B4<=2000),B$1*B$2,0)Nesting If functionsThus far we have used IF functions to evaluate a single logical test and decide between one of two outcomes based on that test. These outcomes may be inserting text into the spreadsheet, displaying a value, or even the result of a nested formula. Frequently problems arise that have more than two outcomes. For example, a ticket for a movie is one price for children, another for adults, and a third for senior citizens. How can that type of problem be solved in Excel? We have already seen how nesting can be used to test for complex criteria or to calculate values where arguments of functions are themselves nested formulas/functions. Nesting can also be used with an IF function to decide between more than two different results. What if the calculation in the value_if_false argument contained another IF function? If the computer reaches this point, because the logical test in the first IF is FALSE, the computer will calculate the value of this 2nd IF and use the result as the FALSE (3rd argument) of the 1st IF. Let’s take a deeper look into how this might work.A Problem with 3 possible outcomes:FALSEFALSETRUETRUEDoes this person earn less than $1000Company Contribution $0Does this person earn at least $1000 but less than $2000Company Contribution 100%Company Contribution 50%Figure 3Again consider of valuing of an employee’s medical benefits, but this time the criteria will be that the employer will pay no benefits to those earning less than $1000, 50% of the premium for those earning at least $1000 but less than $2000, and the full premium for those receiving wages of $2000 or more. In order to decide the value of the company’s benefit contribution it will be necessary to first determine which criterion applies. To understand this problem a little better (step 1), a decision tree has been created in Figure 3 which visually represents the logic in this problem.A diamond represents a decision with two possible outcomes: TRUE or FALSE. Each outcome is either a specific value, such as $0, or leads to another diamond where the next decision is made. In Excel, each of the diamonds can be implemented using an IF function, so the value_if_true and/or the value_if_false can simply be another IF function. Up to 7 levels of nesting can be used in this manner. In this decision tree the first diamond tests to see if John’s wages are less than $1000. If they are then $0 is returned. If John’s wages are not less than $1000 the next diamond will test whether or not John’s wages are at least $1000 but less than $2000. If this is TRUE then a calculation of 50% of the premium cost will be returned; otherwise the entire cost of the premium will be returned.This will be translated into Excel syntax (step 2) based on the worksheet in Figure 4. Figure 4Given the complexity, let’s first translate the decision tree into Excel psuedo code using an IF function:If(John’s wage<1000, $0 for premium, otherwise if (John’s wage is at least $1000 but less than $2000, 50% premium, otherwise 100% premium)). Next, translate this into the proper Excel syntax. Be sure the formula contains an identical number of openning and closing parentheses in the correct places: =If(B4<1000,0,If(B4<2000,B1*0.5,B1))Finally, for step 3 add relative/absolute cell referencing to copy this formula down the column: =IF(B4<1000,0,IF(B4<2000,B$1*0.5,B$1)).This can also be written as IF(B4<1000,0,If(B4<2000,0.5,1))* B$1. Look at the second IF logical test. One may ask why the logical_test doesn’t evaluate that both John’s wage is at least $1000 and that his wage is less than $2000 (i.e., AND(B4>=1000, B4<2000)). To see why it is okay to assume that B4 is greater than or equal to 1000, consider how the function works: First the computer will evaluate the first IF, testing to see if B4<1000. If it is less (TRUE) then the computer will return the answer $0 and the calculation is complete. If the logical test is FALSE (B4 is not less than $1000) Excel will continue evaluating the expression by going to the second IF statement. Thus the value could not possibly be less than $1000 if the second IF is evaluated, so it need not be tested. In some problems, it will matter which case is analyzed first. In this problem each category is mutually exclusive. A wage can only fall into one category (either <$1000, >=$2000 or in between). Therefore the order of the nesting is not important. The formula could have just as easily been written as follows: =IF(B4>=2000,B$1,IF(B4<1000,0,0.5*B$1)). One could also test the “between 1000 and 2000” criteria first, although this would require the more complex logical test using a Boolean function and is not recommended: =IF(And(B4>=1000,B4<2000), 0.5*B$1, IF(B4<1000,0,B$1)).A case where the order would matter is if we set the criteria for the company’s contribution to medical premiums as follows:The company does not pay benefits for management (column E – Category).The company will pay full premiums for hourly employees who earn over $1000 and 50% for those who earn $1000 or less.Here we do not have mutually exclusive categories. A person can earn over $1000 and still not qualify, as in the case of management. So the first logical test must be to determine which employment category an employee is in, and only if they are not in management apply the second set of criteria based on wages. This formula can be written as follows:= IF(E4= “management”,0,IF(B4<=1000,.5*B$1,B$1))203200347980Example 1.8-1 Simple If’s – Buying a USB driveWrite an Excel formula in cell C6 that can be copied down the column to determine if this USB drive is less than the maximum amount (hurdle) you are willing to spend on a USB drive.Write an Excel formula in cell D6 that can be copied down the column to indicate whether or not you will buy this USB drive. The formula should return the text “buy” if you will purchase it (i.e., it costs less than the hurdle amount), and “do not buy” if you will not.Write an Excel formula in cell E6 to determine how much money you would spend, if any, on this USB drive. You will buy a drive if it costs less than the hurdle amount. Make sure you can copy the formula down the column. Alternatively, consider a different buying (and budget) plan as follows: If the cost of the USB drive is less than $20, you will purchase 3 of that drive.If the cost of the USB drive is at least $20 but less than or equal to $35, you will purchase 2 of that drive.If the cost of the USB drive is over $35 you not purchase that drive.Write an Excel formula in cell F6 that can be copied down the column to determine how much money you will spend, if any, on the purchase of these USB drives using this new buying plan.-4381503467104473575346075Example 1.8-2– Travel PricesTrip!Comm!You manage a travel agency in Columbus and have been asked to provide an easy to read table breaking down the pricing of some of your popular travel packages. Travel packages are priced based on their wholesale price plus a markup. The markup is determined by the luxury rating. In addition profit made by the markup, your agency also receives a commission from the airline. Write an Excel formula in cell Trip!D3 that can be copied down the column to determine (True/False) if this trip costs less than the average offering on this list.Write an Excel formula in cell Trip!E3 that can be copied down the column to determine (True/False) if this trip is a luxury trip. Trips in categories A and B are considered to be luxury trips.Write an Excel formula in cell Trip!F3 that can be copied down the column to determine the Retail Price of this trip. The markup for luxury trip packages is 15% of the wholesale price. The markup for non-luxury trip packages is 10% of the wholesale price (as given on sheet comm!). The Retail Price is the wholesale price plus the markup.In addition to the price markup, travel agents also receive a commission from the airline. This commission is a flat fee of $50 per trip from trips costing less than $1000 (based on retail price of trip) or 5% of the wholesale price for trips with a retail price of $1000 or more (as given on sheet comm!). Write an Excel formula in cell Trip!G3 (using cell references wherever possible) to calculate the commission value. Write the formula so it can be copied down the column.Write an Excel formula in cell Trip!H3 that can be copied down the column to determine the profit category of this trip. Trips with commissions of $50 or less commission are in the “low” category, trips with a commission over $50 but less than $100 are in the “medium” category, and trips with a commission of $100 or more are in the “high” category.Write an Excel formula in cell D12 to determine the number of trip packages that cost less than the average wholesale price of the trips on this list. Example 1.8-3– Chapter Review – Discount Pricing34766251743075Data!723900193675Sales!As the owner of a small convenience store you have started to keep track of store sales. For each sale, you will record the discounts which apply to each sale. Your pricing is such that sales are discounted based on the sales amount, as given on the Data! worksheet. The only exception to this is where a senior discount is applied (for those eligible customers over the age of 65). Seniors will receive the greater of either the sales discount or the senior discount. Write an Excel formula in cell Sales!E2 that can be copied down the column to determine the value of the senior discount. Only seniors are eligible for this discount. Senior status is indicated in column C on sheet Sales!. The senior discount percentage in given on sheet Data!. Non-seniors receive no discount. (Note the ‘-‘ display is the currency format for the value $0).Write an Excel formula in cell Sales!F2 that can be copied down the column to determine the value of the sales discount. As indicated on sheet Data!, sales of less than $100 receive no discount, sales of at least $100 but less than $200 receive a 10% discount, and sales of $200 or more receive a 15% discount of the sale amount.Write an Excel formula in cell Sales!G2 that can be copied down the column to determine the discount amount used. The discounted value used will be the greater of the two discounts (senior discount or sales discount). (**challenge – try to do this without an IF).Write an Excel formula in cell Sales!H2 that can be copied down the column to determine total sale value after the discount.Write an Excel formula in cell Sales!I2 that can be copied down the column to determine the sales tax on this item. Sales tax rates are listed by category on sheet Data!. Categories for each sale are listed in column D.Write an Excel formula in cell Sales!J2 that can be copied down the column to determine the value of the sale including tax rounded to the nearest cent. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download