Microsoft Excel Logical Functions Objectives Using Boolean Logic

Microsoft Excel Logical Functions Objectives: Using Boolean Logic in Spreadsheets l l Relational operators l Boolean operators –Functions l None of logical construct Using Conditional Formatting CS&E 1111 Ex. Logic

Boolean Logical Values Is 3 greater than 5 ? The answer is either True or False TRUE and FALSE are Boolean Values Mathematically this would be represented by the expression 3>5 How much greater is 5 than 3? This answer requires an arithmetic value Mathematically this would be represented by the expression 5 - 3 CS&E 1111 Ex. Logic

Excel provides a class of operators known as Relational Operators that can be used to perform a comparison of the left and right sides of an expression <, >, <=, >=, =, <> l l The result is always either TRUE or FALSE Relational Expressions: • =38<=6 results is the value FALSE • =B 2=5+3 results is the value TRUE if B 2 equals 8 CS&E 1111 Ex. Logic

Simple Relational Expressions • Blue’s total is higher than Jones’ grade: =E 4>E 5 • Jones’ total is the maximum in the class: =E 8=E 5 • The average total of students is less than 300: =Average(E 4: E 6)<300 CS&E 1111 Ex. Logic

Precedence of Relational Operators Are these expressions equivalent? =B 4+B 5<=150 =(B 4+B 5)<=150 Relational operators are always evaluated last… CS&E 1111 Ex. Logic

Boolean Logic Operators How do you determine if all of the students got above 320 points to pass the class or if anyone got below 320 points? We can use Boolean Operators - each operator has a corresponding Excel Function AND, OR, NOT CS&E 1111 Ex. Logic

Boolean Logic Operators l l l AND - All items must be true for the statement to be true OR - At least one item must be true for the statement to be true NOT – switches a True to a False and a False to a True CS&E 1111 Ex. Logic

The AND Function Excel uses a Function to perform the AND operation Syntax: =AND(logical 1, logical 2, . . . ) Returns TRUE if all its arguments are TRUE; returns FALSE if one or more arguments is FALSE =AND(TRUE, TRUE) TRUE =AND(F 5, F 6) where cell F 5=TRUE & cell F 6= TRUE =AND(F 5: F 10) where cell F 10=FALSE =AND(3>B 7, 2+4=5) equals the value FALSE where cell B 7=1 For more details on how it treats text or blanks, look at the function on Excel help. CS&E 1111 Ex. Logic

The AND Function Is everyone in the class an Honors student? =And(F 4: F 6) Did everyone pass the course? - the passing score is 320 points = And(E 4>=320, E 5>=320, E 6>=320) CS&E 1111 Ex. Logic

The OR Function Excel uses a Function to perform the OR operation Syntax: =AND(logical 1, logical 2, . . . ) Returns TRUE if any of its arguments are TRUE; returns FALSE only if all arguments are FALSE =OR(TRUE, FALSE) TRUE =OR(F 5, F 6) where cell F 5=FALSE & cell F 6= FALSE =AND(F 5: F 10) where cell F 10=TRUE =AND(3>B 7, 2+4=5) equals the value TRUE where cell B 7=1 For more details on how it treats text or blanks, look at the function on Excel help. CS&E 1111 Ex. Logic

The OR Function Is anyone in the class an Honors student? =OR(F 4: F 6) Did at least one student pass the course the passing score is 320 points = OR(E 4>=320, E 5>=320, E 6>=320) Can we just write OR(E 4: E 6>=20)? CS&E 1111 Ex. Logic

The NOT Function Excel uses a Function to perform the NOT operation Syntax: =NOT(logical) Reverses the value of its argument. Turns a TRUE to a FALSE - and a FALSE to a TRUE. Use NOT when you want to make sure a value is not equal to one particular value. A NOT function takes only ONE argument =NOT(FALSE) equals the value TRUE =NOT(F 3) where F 3=True equals the value FALSE =NOT(3>5) equals TRUE CS&E 1111 Ex. Logic

The NOT Function Blue is not an Honors student =NOT(F 4) Blue didn’t get a passing grade (320)? =NOT(E 4>=320) is the same as E 4<320 CS&E 1111 Ex. Logic

How can we determine if none of the students are Honors students? Prove at least one student is an honors student (TRUE) then you know the statement none is FALSE = NOT(OR(F 4: F 6)) Another way is to prove each one is not an honor’s student = AND(NOT(F 4), Not(F 5), Not(F 6)) CS&E 1111 Ex. Logic

re o M e c i ct a r P • Blue’s total and Jones’ total are greater than Grey’s: =AND(E 4>E 6, E 5>E 6) FALSE • Grey’s total is not the maximum grade: =NOT(E 6=MAX(E 4: E 7)) TRUE • None of the students passed the course = NOT(OR(G 4: G 5)) TRUE CS&E 1111 Ex. Logic

Conditional Formatting “Conditional Formatting” allows the user to format a cell or range of cells based on specific criteria. The user has the option to setup criteria based on a value in a cell or based on a formula. CS&E 1111 Ex. Logic

What we’ve learned using Boolean Logical Constructs in Decision Making l l A relational expression can be used to compare two values. To determine if a list of logical arguments are ALL true use the AND function. (every, both) To determine if at least one value is TRUE from a list of logical arguments use the OR function. (any, some) Use the NOT function to change a TRUE value to FALSE and visa versa. Combine a NOT(OR() construct to determine if none of the values are TRUE. CS&E 1111 Ex. Logic