Report

259 Lecture 4 Spring 2010 Logical Functions and Conditional Formatting in Excel Logical Functions Excel’s built-in logical functions are useful for introducing conditional statements, i.e. “decision making” functions into a spreadsheet! The functions are: TRUE FALSE NOT AND OR IF IFERROR (new in Excel 2007) Information on each of these functions can be found in the Help File or at Microsoft’s Office Web pages: http://office.microsoft.com/en-us/excel/HP100791861033.aspx 2 The TRUE Function Syntax: TRUE( ) Returns the logical value TRUE. TRUE can be entered directly into cells and formulas without using this function. 3 The FALSE Function Syntax: FALSE( ) Returns the logical value FALSE. FALSE can be entered directly into cells and formulas without using this function. 4 The NOT Function Syntax: NOT(logical) logical is a value or expression that can be evaluated to TRUE or FALSE. Reverses the logical value of its argument. If logical is FALSE, NOT returns TRUE. If logical is TRUE, NOT returns FALSE. 5 Example 1 Try NOT on the following: 0, 1, a, FALSE, 1+1=2. 6 Example 1 (cont.) 7 The AND Function Syntax: AND(logical1,logical2, ...) logical1, logical2, ... are 1 to 255 conditions you want to test that can be either TRUE or FALSE. Returns TRUE if all its arguments are TRUE. Returns FALSE if one or more argument is FALSE. 8 The AND function (cont.) The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays (i.e. rectangular blocks of cells) or references that contain logical values. If an array or reference argument contains text or empty cells, those values are ignored. If the specified range contains no logical values, AND returns the #VALUE! error value. 9 Example 2 Make an AND truth table in Excel! AND TRUE FALSE TRUE TRUE FALSE FALSE FALSE FALSE 10 Example 2 (cont.) 11 The OR Function Syntax: OR(logical1,logical2,...) logical1, logical2, ... are 1 to 255 conditions you want to test that can be either TRUE or FALSE. Returns TRUE if any argument is TRUE. Returns FALSE if all arguments are FALSE. 12 The OR Function (cont.) The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays or references that contain logical values. If an array or reference argument contains text or empty cells, those values are ignored. If the specified range contains no logical values, OR returns the #VALUE! error value. 13 Example 3 Make an OR truth table in Excel! OR TRUE FALSE TRUE TRUE TRUE FALSE TRUE FALSE 14 Example 3 (cont.) 15 The IF function Syntax: IF(logical_test,value_if_true,value_if_false) logical_test is any value or expression that can be evaluated to TRUE or FALSE. value_if_true is the value that is returned if logical_test is TRUE. value_if_false is the value that is returned if logical_test is FALSE. 16 Example 4 1. 2. 3. 4. 5. 6. 7. 8. Try each of the following IF statements: =IF(1 < 2, “Mickey Mouse”, “Donald Duck”) =IF(1 > 2, “Mickey Mouse”, “Donald Duck”) =IF(1 <= 2,) =IF(1 <> 2, TRUE) =IF(1 < 2, 6*2+5) =IF(1 >= 2,) =IF(1 > 2, TRUE) =IF(“yes” = “no”, 6*2+5, 2-10^3) 17 Example 4 (cont.) 18 The IF function (cont.) If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for value_if_true. value_if_true can be another formula. If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. value_if_false can be another formula. 19 Example 5 Use IF to create a piecewise-defined function for the absolute value function. 20 Example 6 Use IF to assign grades automatically in a grade book! If score ≥ 93, then assign “A”. If score < 93, then assign “Not A”. 21 Example 7 First class postage rates are given below. Use a nested IF statement to help calculate postage! First-Class Mail (Retail Letters) - Postage Rates Weight not Over (Ounces) Single-Piece 1 $0.41 2 $0.58 3 $0.75 3.5 $0.92 22 Example 7 (cont.) The idea we need to implement in Excel is: Weight ≤ 1 oz => $0.41 1 oz < Weight ≤ 2 oz => $0.58 2 oz < Weight ≤ 3 oz => $0.75 3 oz < Weight ≤ 3.5 oz => $0.92 Over 3.5 oz => cannot mail first class! To do this we can use a nested IF command! =IF(CELL#<=1,0.41,IF(CELL#<=2,0.58,IF(CELL#<= 3,0.75,IF(CELL#<=3.5,0.92,"cannot mail first class")))) 23 Example 7 (cont.) 24 The IF Function (cont.) Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements. If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out. Microsoft Excel provides additional functions that are conditional, including COUNTIF, SUMIF, and others. Another way to test many conditions is to use the LOOKUP, VLOOKUP, or HLOOKUP functions. 25 The LOOKUP Function LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. Syntax: LOOKUP(lookup_value,lookup_vector,result_vector) lookup_value is a value that LOOKUP searches for in the first vector. lookup_vector is a range that contains only one row or one column. result_vector is a range that contains only one row or column. It must be the same size as lookup_vector. 26 The LOOKUP Function (cont.) lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. The values in lookup_vector can be text, numbers, or logical values. The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent. If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value. 27 Example 8 The LOOKUP function can be used to calculate postage rates! Try this command with the weights we used above in Example 7: 0.5, 1, 1.1, 2, 2.1, 3, 3.1, and 4 ounces. =LOOKUP(weight,{0,1,2,3,3.5},{0.41, 0.58,0.75,0.92,"cannot mail first class"}) 28 Example 8 (cont.) Does the postage get calculated correctly? Notice that the postages for 1, 2, and 3 ounces are wrong. How could we fix our LOOKUP function to get the correct postages? One possibility – replace 1 with 1.000001, 2 with 2.000001, etc. in the look_up vector. 29 The IFERROR Function Syntax: IFERROR(value,value_if_error) value is the argument that is checked for an error. value_if_error is the value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. 30 The IFERROR Function (cont.) If value or value_if_error is an empty cell, IFERROR treats it as an empty string value (""). If value is an array formula, IFERROR returns an array of results for each cell in the range specified in value. 31 Example 9 Use the IFERROR function to create a “Division by Zero Checker” What happens if any of the cells in columns A or B are blank? 32 Conditional Formatting The formatting of a cell can be a determined by the cell contents! In the Styles group of the Home tab, choose the Conditional Formatting drop-down menu. The following examples are formatted conditionally, i.e. in terms of their contents, by choosing some of the built-in conditional formatting rules (large number available is new in Excel 2007)! Highlight Cells Rules -> Less Than Highlight Cells Rules -> Text that Contains Data Bars Top/Bottom Rules -> Above Average 33 Conditional Formatting (cont.) Suppose we wish to use colors to highlight grades in a grade sheet, using colors to differentiate ranges of scores. In particular, choose yellow, red, and green as the colors determined by scores in the range: score ≥ 92, 90 ≤ score < 92, score < 90, respectively! One way to try to do this is via a built-in conditional formatting Color Scale, such as Green-Yellow-Red. The drawback is that the built-in formatting may not match exactly what you want! 34 Conditional Formatting (cont.) The other way is to create new conditional formatting rules! To do this, choose Manage Rules from the Conditional Formatting Menu to pull up the Conditional Formatting Rules Manager. Then choose New Rule, followed by Format Only Cells that Contain to set up the appropriate conditions. 35 References Excel’s Help File and Microsoft’s Office Web pages: http://office.microsoft.com/enus/excel/HP100791861033.aspx About.com Excel IF Tutorial: http://spreadsheets.about.com/od/ex celfunctions/a/if_funct_hub.htm Microsoft Excel 2007 Bible by John Walkenbach 36