How to Use NOT Function in Google Sheets in Logical Test

0
118
How to Use NOT Function in Google Sheets in Logical Test

I’m not remembering when I had last used NOT logical function in any formula in Google Sheets. The reason, I was just satisfied with the comparison operators as an alternative to NOT. Didn’t get? Let’s see how to use NOT function in Google Sheets and the alternative comparison operators. I am going to nest IF, AND, OR and NOT here.

In Google Sheets, in most of the cases you can use the <> operator to replace the NOT function. But the NOT function in Google Sheets has certain advantages over the <> comparison operator. Let’s see that here.

How to Use NOT Function in Google Sheets

Syntax:

NOT(logical_expression)

Purpose:

In concise the NOT logical function returns the opposite of a logical value.

Example Formula

We can first test different values in cell A1 with different NOT formulas. After that, we can learn how to use NOT function in logical tests that involving functions like AND, OR, IF.

Similar: Use of IF, AND, OR Logical Formulas as Nested Formula in Google Sheets

Cell Value in A1 is 0;

Formula: =NOT(A1)
Result: TRUE

Cell Value in A1 is 20;

Formula: =NOT(A1)
Result: FALSE

Explanation to the above two NOT formulas:

As you may know 0 has a logical value of FALSE. Similarly any non-zero numeric (it can be any number, negative or positive) value has a logical value of TRUE. The above NOT formula reverse the result from TRUE to FALSE and vice versa. Needless to say when non-numeric values are entered in Cell A1, the above formula would return the error “#VALUE!”.

In certain calculations or logical tests the formula in use may some times return “#N/A”, or “#DIV/0!” like errors. In such cases, you may be familiar in using ISERROR or IFERROR functions to check whether the output of a calculation is error. If not see one example below.

Value in Cell A1 is 27 and in B1 is 0.

Formula: =A1/B1
Result: #DIV/0!

Now the same above formula with ISERROR.

Formula: =iserror(A1/B1)
Result: TRUE

Here if you use Google Sheets NOT function with the above formula, it would return FALSE.

Formula: =NOT(iserror(A1/B1))
Result: FALSE

You may ask why all these required. I’ll explain it. If you want to return 0 instead of an error value in a cell, you can use a formula as below.

Formula: =NOT(iserror(A1/B1))
Result: 0

This formula would return 0 when error passes to it by another formula, else 1. As a side note, if you want to return 0 when an error occurs, no need to use NOT. You can straightaway use the IFERROR function as below.

NOT function advanced use in Google Sheets

There are three different formulas in C18, D18, and E18 as below.

Formula in C18
=A18/B18
Formula in D18
=iferror(A18/C18,0)
Formula in E18
=–NOT(iserror(A18/B18))

You can see the above formula results in C18, D18 and E18. In order to include the entire range C18:C24, D18:D24 and E18:E24 in the formulas I have used array formulas as below.

C18
=ArrayFormula(A18:A24/B18:B24)
D18
=ArrayFormula(iferror(A18:A24/C18:C24,0))
E18
=ArrayFormula(–NOT(iserror(A18:A24/B18:B24)))

When you go through the results (in Column E) you can understand how to use NOT function in Google Sheets especially in logical tests. Further, you can consider the above formula in E18 as an example to the use of Google Sheets NOT formula in Array.

Before concluding this tutorial one more example. Now we can see how to use NOT in IF, AND, OR nested formula.

How to Nest IF, AND, OR and NOT in Google Sheets

Cell Value in A4 = “inspired”

I am using comparison operator here to perform a logical test in cell A4. The result I want is “Yes” or “No” based on the value in A4. If A4 doesn’t contain either of the word “info” or “inspired” I want the formula to return “No”. In other words if the cell value in A4 is either “info” or “inspired” the result should be “Yes” other wise “No”. You can use three formulas here. Obviously the third formula is clean and not recommended to use NOT here.

=if(and(A4<>”info”,A4<>”inspired”),”No”, “Yes”)
=IF(NOT(OR(A4=”Info”,A4=”Inspired”)),”No”,”Yes”)
=if(or(A4=”info”,A4=”inspired”),”Yes”,”No”)

Hope you could understand how to use NOT function in Google Sheets. Similarly you can use NOT with AND. But as I told you in the beginning of this post, in most cases you can simply use <> comparison operator or reversing AND to OR or OR to AND. NOT is always not a must.

LEAVE A REPLY

Please enter your comment!
Please enter your name here