1 / 17

Microsoft Excel Logical Functions

Microsoft Excel Logical Functions. Objectives: Using Boolean Logic in Spreadsheets Relational operators Boolean operators –Functions None of logical construct Using Conditional Formatting. Boolean Logical Values. Is 3 greater than 5 ? The answer is either True or False

byron
Download Presentation

Microsoft Excel Logical Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft ExcelLogical Functions • Objectives: Using Boolean Logic in Spreadsheets • Relational operators • Boolean operators –Functions • None of logical construct • Using Conditional Formatting

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

  3. 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 <, >, <=, >=, =, <> • The result is always either TRUE or FALSE • Relational Expressions: • =38<=6 results is the value FALSE • =B2=5+3 results is the value TRUEif B2 equals 8

  4. Simple Relational Expressions • Blue’s total is higher than Jones’ grade: =E4>E5 • Jones’ total is the maximum in the class: =E8=E5 • The average total of students is less than 300: • =Average(E4:E6)<300

  5. Precedence of Relational Operators Are these expressions equivalent? =B4+B5<=150 =(B4+B5)<=150 Relational operators are always evaluated last…

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

  7. Boolean Logic Operators • 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

  8. The AND Function Excel uses a Function to perform the AND operation Syntax: =AND(logical1,logical2, ...) Returns TRUE if all its arguments are TRUE; returns FALSE if one or more arguments is FALSE =AND(TRUE, TRUE)TRUE =AND(F5,F6)where cell F5=TRUE & cell F6= TRUE  TRUE =AND(F5:F10)where cell F10=FALSE  FALSE =AND(3>B7, 2+4=5)equals the value FALSE where cell B7=1 For more details on how it treats text or blanks, look at the function on Excel help.

  9. The AND Function Is everyone in the class an Honors student? =And(F4:F6) Did everyone pass the course? - the passing score is 320 points = And(E4>=320, E5>=320, E6>=320)

  10. The OR Function Excel uses a Function to perform the OR operation Syntax: =AND(logical1,logical2, ...) Returns TRUE if any of its arguments are TRUE; returns FALSE only if all arguments are FALSE =OR(TRUE, FALSE)TRUE =OR(F5,F6)where cell F5=FALSE & cell F6= FALSE FALSE =AND(F5:F10)where cell F10=TRUE  TRUE =AND(3>B7, 2+4=5)equals the value TRUE where cell B7=1 For more details on how it treats text or blanks, look at the function on Excel help.

  11. The OR Function Is anyone in the class an Honors student? =OR(F4:F6) Did at least one student pass the course - the passing score is 320 points = OR(E4>=320, E5>=320, E6>=320) Can we just write OR(E4:E6>=20)?

  12. 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(F3) where F3=True equals the value FALSE • =NOT(3>5) equals TRUE

  13. The NOT Function Blue is not an Honors student =NOT(F4) Blue didn’t get a passing grade (320)? =NOT(E4>=320) is the same as E4<320

  14. How can we determine ifnoneof the students are Honors students? Prove at least one student is an honors student (TRUE) then you know the statement noneis FALSE = NOT(OR(F4:F6)) Another way is to prove each one is not an honor’s student = AND(NOT(F4), Not(F5), Not(F6))

  15. More Practice • Blue’s total and Jones’ total are greater than Grey’s: • =AND(E4>E6, E5>E6)FALSE • Grey’s total is not the maximum grade: • =NOT(E6=MAX(E4:E7))TRUE • None of the students passed the course • = NOT(OR(G4:G5))  TRUE

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

  17. What we’ve learned using Boolean Logical Constructs in Decision Making • A relational expressioncan 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.

More Related