Logical functions - worksheet
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
-
Fig 2: Marks grades from a university handbook. Students are awarded a grade based on the mark achieved - 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], ...)
=OR(TRUE(),TRUE(),TRUE())returns a logical TRUE=OR(TRUE(),TRUE(),FALSE())returns a logical TRUE=OR(FALSE(),FALSE(),FALSE())returns a logical FALSE
=OR(TRUE(),TRUE())returns a logical TRUE=OR(TRUE(),FALSE())returns a logical TRUE=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], ...)
=XOR(TRUE(),TRUE())returns a logical FALSE=XOR(TRUE(),FALSE())returns a logical TRUE=XOR(FALSE(),FALSE())returns a logical FALSE
Comparison AND, OR, and XOR
AND returns TRUE if all are TRUE
OR returns TRUE if at least one is TRUE
XOR returns TRUE if either one is TRUE (pair-wise)
Note: the formula in cell L9 is: =IF(ISFORMULA(H9),ADDRESS(ROW(H9),COLUMN(H9),4)&": "&FORMULATEXT(H9),ADDRESS(ROW(H9),COLUMN(H9),4)&": "&IF(NOT(ISBLANK(H9)),OneLeft,""))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)
=NOT(TRUE())returns a logical FALSE=NOT("up"="down")returns a logical TRUE
- This example was developed in Excel 2013 Pro 64 bit.
- Published: 23 March 2015
- Revised: Saturday 25th of February 2023 - 10:13 AM, [Australian Eastern Standard Time (EST)]