259 Lecture 4 Spring 2010

1. 259 Lecture 4 Spring 2010 Logical Functions and Conditional Formatting in Excel

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

3. The TRUE Function • Syntax: TRUE( ) • Returns the logical value TRUE. • TRUE can be entered directly into cells and formulas without using this function.

4. The FALSE Function • Syntax: FALSE( ) • Returns the logical value FALSE. • FALSE can be entered directly into cells and formulas without using this function.

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

6. Example 1 • Try NOT on the following: 0, 1, a, FALSE, 1+1=2.

7. Example 1 (cont.)

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

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

10. Example 2 • Make an AND truth table in Excel!

11. Example 2 (cont.)

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

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

14. Example 3 • Make an OR truth table in Excel!

15. Example 3 (cont.)

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

17. Example 4 • 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)

18. Example 4 (cont.)

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

20. Example 5 • Use IF to create a piecewise-defined function for the absolute value function.

21. 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”.

22. Example 7 • First class postage rates are given below. • Use a nested IF statement to help calculate postage!

23. 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"))))

24. Example 7 (cont.)

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

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

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

28. 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"})

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

30. 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!.

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

32. 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?

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

34. 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!

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

36. References • Excel’s Help File and Microsoft’s Office Web pages: http://office.microsoft.com/en-us/excel/HP100791861033.aspx • About.com Excel IF Tutorial: http://spreadsheets.about.com/od/excelfunctions/a/if_funct_hub.htm • Microsoft Excel 2007 Bible by John Walkenbach