Logical

All logical functions evaluate the existence of a Boolean value TRUE or FALSE value, and comprise of a logical_test based on a comparison operator.

Comparison operators

Excel comparison operators	Description
= equals (eq)	Returns TRUE if Value1 is equal to Value2. eg. Value1=Value2
< less than (lt)	Returns TRUE if Value1 is less than Value2. eg. Value1<Value2
> greater than (gt)	Returns TRUE if Value1 is greater than Value2. eg. Value1>Value2
<= less than or equals (lte)	Returns TRUE if Value1 is less than or equal to Value2. eg. Value1<=Value2
>= greater than or equals (gte)	Returns TRUE if Value1 is greater than or equal to Value2. eg. Value1>=Value2
<> not equal (ne)	Returns TRUE if Value1 is not equal to Value2. eg. Value1<>Value2

Logical functions

Logical functions are grouped under the Logical category in the Excel function library, and the Insert Function dialog box.

*/ ?>

Excel logical functions	Description
IF	Returns one value if the logical test is TRUE, and another value if the logical test is FALSE
AND	Returns TRUE if all arguments return TRUE
OR	Returns TRUE if any argument returns TRUE
XOR [eXclusive OR; New in Excel 2013]	Returns the logical Exclusive Or of all arguments
TRUE	Returns the logical value TRUE
FALSE	Returns the logical value FALSE
NOT	Returns the reverse of its logical argument
IFERROR	Returns the value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
IFNA	Returns the logical TRUE if value refers to a #N/A error, else it returns FALSE

Examples

IF function

• IF function
• SYNTAX: IF(logical_test, [value_if_true] [,value_if_false])

Example: Suppose that the vector named \(Vector\) contains the series \(\{1,2,…9,10\}\) with count of 10 and average value of 5.5. We will now use a series of logical tests to determine if the values 3, and 7 are less than the average, 5.5.

• Logical
• =3<AVERAGE(Vector) returns TRUE
• =7<AVERAGE(Vector) returns FALSE

The IF function has optional arguments, but is most commonly used with three arguments.

• IF (one argument)
• =IF(3<AVERAGE(Vector),) returns 0
• =IF(7<AVERAGE(Vector),) returns FALSE
• IF with one argument provides NO advantages compared to the simple logical operator statement above.
• In addition, IF with one argument requires a trailing comma, this is rather unusual; and if the logical_test is TRUE, the function returns 0 (ZERO), rather than the logical TRUE

• IF (two arguments)
• =IF(3<AVERAGE(Vector),"Low") returns the text string Low
• =IF(7<AVERAGE(Vector),"Low") returns FALSE because the third argument has been omitted

• IF (three arguments)
• =IF(3<AVERAGE(Vector),"Low","High") returns the text string Low
• =IF(7<AVERAGE(Vector),"Low","High") returns the text string High

Nested IF functions

A example – a university handbook describes the marks and grades relationship as shown in figure 1.

Mapping marks to grades, from figure 1, using the worksheet IF function.

• In this section, the IF structure is written is ascending order – to mimic the structure of a VLOOKUP function nearest match
• Formula: =IF(Marks<50,"N",IF(Marks<65,"P",IF(Marks<70,"H3",IF(Marks<75,"H2B",IF(Marks<80,"H2A","H1"))))), as an array formula, with a test vector named Marks. The array formula for the Grades is a 13 x 1 target, based on the dimensions of the Marks array
• Each nested IF structure returns the first TRUE test then exists
•  

• 

• Excel permits 64 levels of nesting. The marks grades example contains 5 IF functions - with 4 levels of nesting
• In general, if the statements include more than 3 or 4 nesting levels, it is easier to use a Lookup and Reference function, or use a VBA procedure

AND function w/ the TRUE function and FALSE function

• TRUE function
• SYNTAX: TRUE()

• FALSE function
• SYNTAX: FALSE()

• AND function - ALL TRUE
• SYNTAX: AND(logical1, [logical2], ...)

• =AND(TRUE(),TRUE(),TRUE() returns a logical TRUE
• =AND(TRUE(),TRUE(),FALSE()) returns a logical FALSE
• =AND(FALSE(),FALSE(),FALSE()) returns a logical FALSE

OR function

• OR function - ANY ONE TRUE
• SYNTAX: OR(logical1, [logical2], ...)

1. =OR(TRUE(),TRUE(),TRUE()) returns a logical TRUE
2. =OR(TRUE(),TRUE(),FALSE()) returns a logical TRUE
3. =OR(FALSE(),FALSE(),FALSE()) returns a logical FALSE

1. =OR(TRUE(),TRUE()) returns a logical TRUE
2. =OR(TRUE(),FALSE()) returns a logical TRUE
3. =OR(FALSE(),FALSE()) returns a logical FALSE

Logically, an OR function should return TRUE when one OR the other of two functions is TRUE. In Excel, this functionality was introduced with the XOR (Exclusive Or) function. This is shown by comparing points 1 to 3 (above) using OR, to points 4 to 6 (below) using XOR.

XOR function

• XOR function - only ONE TRUE
• SYNTAX: OR(logical1, [logical2], ...)

4. =XOR(TRUE(),TRUE()) returns a logical FALSE
5. =XOR(TRUE(),FALSE()) returns a logical TRUE
6. =XOR(FALSE(),FALSE()) returns a logical FALSE

Comparison AND, OR, and XOR

NOT function

The NOT function is useful when it is easier to write a particular logical test, then reverse the answer. An example is an advanced filter criteria for data filtering.

• NOT function
• SYNTAX: NOT(logical)

1. =NOT(TRUE()) returns a logical FALSE
2. =NOT("up"="down") returns a logical TRUE