Excel Logical Functions

sample files

1. AND Function

AND function returns a Boolean value (TRUE or FALSE) after testing conditions you specify. In simple words, you can test multiple conditions with AND function and it returns TRUE if all those conditions are TRUE, else FALSE.

Syntax

AND(logical1, [logical2], …)

Arguments

  • logical1: Condition which you want to verify.
  • [logical2]: Additional Conditions you want to verify.

Notes

  • Values will be ignored if the reference cell or array contained an empty cell or a text.
  • It will return an error if there is no logical value is returned that means the result of conditions should be in logical value (TRUE or FALSE).
  • The maximum number of values you can test is 255.

Example

In the below example, we have created a condition using IF function that if a student score 60 above marks in both of the subjects then only it will return TRUE else FALSE.

excel-and-function-example-2

You can also use AND function to work with numbers as well.

2. FALSE Function

FALSE function returns a logical value FALSE (Boolean). The FALSE return by the FALSE function is the same as you enter FALSE in a cell manually and it is equivalent to the numeric value 0.

Syntax

FALSE()

Arguments

  • It has no arguments.

Notes

  • It returns the same TRUE which you can get by simply typing it in a cell.

Example

In the below example, we have used FALSE() and FALSE, in the same manner, and both return the same value. You can also use FALSE in the numeric calculation as it has 0 value.

excel-false-function-example-1

3. IF Function

IF Function returns a value if the condition you specify is TRUE, else some other value. In simple words, the IF function can test a condition first and returns a value based on the result of that condition.

Syntax

IF(logical_test,value_if_true,value_if_false)

Arguments

  • logical_test: The condition which you want to evaluate.
  • value_if_true: The value which you want to get if that condition is TRUE.
  • value_if_false: The value which you want to get if that condition is FALSE.

Notes

  • The maximum number of nested conditions you can perform is 64.
  • You can use comparison operators to evaluate a condition.

Example

In the below example, we have used a comparison operator to evaluate different conditions.

excel-if-function-example-1
  1. We have used a specific text to get in the result if the condition met or not.
  2. You can also use TRUE and FALSE to get in result.
  3. If you skip specifying a value to get the result if the condition is TRUE, it will return zero.
  4. And if you skip specifying a value to get the result if the condition is FALSE, it will return zero.

In the below example, we have used the IF function to create a nesting formula.

excel-if-function-example-2

We have specified a condition and if that condition is false then we have used another IF to evaluate another condition and perform a task and if that condition is FALSE we have used another IF. In this way, we have used IF five times to create a nesting formula. You can use the same for 64 times for a nesting formula.

4. IFERROR Function

IFERROR function returns a specific value if an error occurs. In simple words, it can test value and if that value is an error it returns the value you have specified.

Syntax

IFERROR(value, value_if_error)

Arguments

  • value: The value you want to test for the error.
  • value_if_error: The value which you want to get in return when an error occurs.

Notes

  • IFERROR function is concerned with the occurrence of an error, not with the type of the error.
  • If you skip specifying value or value_if_error, it will return 0 in the result.
  • It can test #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.
  • If you are evaluating an array it will return an array of results for each item specified.

Example

In the below example, we have used the IFERROR function to replace the #DIV/0! with some meaningful text.

excel-iferror-function-example-1

IFERROR is only compatible with 2007 and earlier versions. To deal with this problem, you can use ISERROR.

5. IFNA Function

IFNA function returns a specific value if an #N/A error occurs. Unlike IFERROR, it only evaluates the #N/A error and returns the value you specified.

Syntax

IFNA(value, value_if_na)

Arguments

  • value: The value you want to test for #N/A error.
  • value_if_na: The value you want to return if an error occurred.

Notes

  • If you skip specifying any argument, IFNA will treat it as an empty string (“”).
  • If a value is an array then it will return the result as an array.
  • It will ignore all other errors #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.

Example

In VLOOKUP function, #N/A occurs when the lookup value is not in the lookup range and for that we have specified a meaningful message using IFNA.

excel-ifna-function-example-1

Note: IFNA is introduced in Excel 2013 so it is not available in the previous versions.

6. NOT Function

NOT function returns the reversed logical value in the result. In simple words, you have a logical value that returns TRUE, NOT can convert it into FALSE and if you have a logical value which returns FALSE, NOT function will convert it into TRUE.

Syntax

NOT(logical)

Arguments

  • logical: The value you want to test to reverse the logical value.

Notes

  • If you refer to a blank cell it will treat that value as a TRUE.

Example

In the below example, we have used NOT to reverse the logical results.

excel-not-function-example-1

7. OR Function

OR Function returns a Boolean value (TRUE or FALSE) after testing conditions you specify. In simple words, you can test multiple conditions with AND function and it returns TRUE if any of those (or all) conditions is TRUE and returns FALSE only if all those conditions are FALSE.

Syntax

OR(logical1, [logical2], …)

Arguments

  • logical1: Condition which you want to verify.
  • [logical2]: Additional Conditions you want to verify.

Notes

  • Values will be ignored if the reference cell or array contained an empty cell or text.
  • The result of conditions should be in logical value (TRUE or FALSE).
  • It will return an error if there is no logical value is returned.

Example

In the below example, we have created a condition using IF function that if a student score 60 above marks in one of the both of the subjects the formula returns TRUE.

excel-or-function-example-1

Now in the below example, we have used a number to get logical values in a formula. You can also perform the above condition in reverse order. You can use TRUE and FALSE instead of numbers. OR function treats these logical values as numbers.

8. TRUE Function

TRUE function returns a logical value TRUE (a boolean). The TRUE return by the TRUE function is the same as you enter TRUE in a cell manually and it is equivalent to the numeric value 1.

Syntax

TRUE()

Arguments

  • It has no arguments.

Notes

  • TRUE and TRUE() both are identical.
  • TRUE has a value of 0.
  • Using TRUE without parentheses will also give you the same result.

Example

In the below example, we have used TRUE() and TRUE, in the same manner, and both return the same value. You can also use TRUE in the numeric calculation as it has 1 value.

excel-true-function-example-1