Free Online Tutorials on Spreadsheets

Logical functions in Excel

What is a logical function?

It is a feature that allows to test one or more conditions while analyzing data in Excel and return either TRUE if the condition is met or FALSE if not. For example, if cell A1 contains 60 or above marks, return “Pass” otherwise “Fail” if marks are below the defined condition.

To illustrate most commonly used logical functions, examples are provided here-under:

IF

The IF function checks whether a specified condition is met or not, If met return one value otherwise second value if false.

For example, look at the IF function in C2 cell below:

Result. The function will return “Close” if B2 cell is filled otherwise return “Open”.

For more detail on the function, visit the IF function.

AND

The AND function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false

For example, you can highlight all those numeric values which are less than 150 and greater than 100.

AND-function-in-Excel.png (328×127)

Result. The function returns “TRUE” if both conditions are met otherwise “FALSE”

AND-function-in-Excel-step-2.png (224×128)

OR

The OR function tests multiple logical conditions at the same time.

For example, if value in cell A2 is greater than 10 and smaller than 40 in cell B2, return “TRUE” otherwise if both conditions are not met return “FALSE”.

OR-function-in-Excel.png (269×86)

NOT

The NOT function is a logical function and returns the opposite of a given logical value i.e. if supplied with the value TRUE, the function returns the FALSE and vice-versa.

For example, return “TRUE” if the value in A2 is not greater than 11.

Example 1

The NOT function also increase the usefulness of other logical functions like:

Example 2

IFERROR

The IFERROR function a specified value if an error found; otherwise, returns the result of the formula. It is a useful function to handle errors in Excel.

For example, when a number is divided by 0, Excel returns the DIV/0! error.

To handle the DIV/0! error, the IFERROR function will first check for the error, if found then display the message “Error in calculation”.

Result.

TRUE or FALSE

Both TRUE and FALSE functions are used for compatibility with other spreadsheet programs. These functions return the Boolean value TRUE and FALSE.




Copyright © 2016 - 2019 Explainry.com | All Rights Reserved