Microsoft Excel Logical Functions

1
Microsoft ExcelLogical Functions

• Objectives
• Using Boolean Logic in Spreadsheets
• Relational operators
• Boolean operators Functions
• None of logical construct

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 3gt5
• 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

• lt, gt, lt, gt, , ltgt
• The result is always either TRUE or FALSE
• Relational Expressions
• 38lt6 results is the value FALSE
• B253 results is the value TRUE if B2 equals 8

4
Precedence of Relational Operators

• Relational operators have lower precedence than
  arithmetic operators
• The expression
• (10 5) lt 30 is the same as 10 5 lt 30

5
Simple Relational Expressions

• Blues total is higher than Jones grade
• E4gtE5 (FALSE)
• Jones total is the maximum in the class
• Max(E4E6)E5 (TRUE)
• The average total of students is less than 300
• Average(E4E6)lt300 (FALSE)

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
Understanding Logical Expressions
Entire area - all OSU students
Honor students
cse101 students

• Colored circle striped area - all cse101
  students at OSU
• White circle striped area - all Honor students
  at OSU
• Both circles including striped area - Honors
  student or cse101 student
• Striped circle only cse101 student and honors
  student
• All areas but the white circle striped area -
  not an Honor student

9
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
• Up to 30 logical arguments can be used - each
  one must evaluate to logical values such as TRUE
  or FALSE
• AND(TRUE, TRUE) equals the value TRUE
• AND(F5, F6) where cell F5TRUE cell F6 FALSE
  equals the value FALSE
• AND(3gtB7, 245) equals the value FALSE where
  cell B71

10
The AND Function
Did everyone pass the course? - the passing score
is 320 points And(E4gt320, E5gt320,
E6gt320) Is everyone in the class an Honors
student? And(F4F6)
11
The OR Function
Excel uses a Function to perform the OR
operation Syntax OR(logical1,logical2,
...) Returns TRUE if any one of its arguments are
TRUE returns FALSE if all the arguments are
FALSE Up to 30 logical arguments can be used -
each one must evaluate to logical values such as
TRUE or FALSE OR(FALSE, FALSE) equals the value
FALSE OR(F5, F6) where cell F5True cell F6
FALSE equals the value
True OR(3gtB7, 245) equals the value True where
cell B71
12
The OR Function
Did at least one student pass the course - the
passing score is 320 points OR(E4gt320,
E5gt320, E6gt320) Is anyone in the class an
Honors student? OR(F4F6)
13
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 F3True equals the value
FALSE NOT(3gt5) equals TRUE
14
The NOT Function
Blue didnt get a passing grade
(320)? NOT(E4gt320) is the same as E4lt320 Blue
is not an Honors student NOT(F4)
15
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(F4F6)) Another way is to prove
each one is not an honors student AND(NOT(F4),
Not(F5), Not(F6))
16
More Practice (Stocks)

• Blues total and Jones total are greater than
  Greys
• AND(E4gtE6, E5gtE6) returns a FALSE
• Greys total is not the maximum grade
• NOT(E6MAX(E4E7)) returns a TRUE
• None of the students passed the course
• NOT(OR(G4G5))

17
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.
18
What weve learned using Boolean Logical
Constructs in Decision Making

• A relational expression can be used to compare
  two values. The resulting value is either TRUE
  or FALSE.
• To determine if a list of logical arguments are
  ALL true use the AND function.
• To determine if at least one value is TRUE from a
  list of logical arguments use the OR function.
• Use the NOT function to change a TRUE value to
  FALSE and visa versa.