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)
The Purpose of the NOT Function in Google Sheets
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;
=NOT(A1)
Result: TRUE
Cell Value in A1 is 20;
=NOT(A1)
Result: FALSE
Explanation of 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 reverses 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 in logical tests the formula in use may sometimes return “#N/A”, or “#DIV/0!” like errors.
In such cases, you may be familiar with using ISERROR or IFERROR functions to check whether the output of a calculation is an error. See one example below.
The value in Cell A1 is 27 and in B1 is 0.
=A1/B1
Result:Â #DIV/0!
Now the same above formula with ISERROR.
=iserror(A1/B1)
Result: TRUE
Here if you use Google Sheets NOT function with the above formula, it would return FALSE.
=NOT(iserror(A1/B1))
Result: FALSE
You may ask why all these are 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.
=--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 straight away use the IFERROR function as below.
How to Return 0 (zero) When Formula Returns Error
There are three different formulas in C18, D18, and E18 as below.
1. Formula in C18
=A18/B18
2. Formula in D18
=iferror(A18/C18,0)
3. 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 of 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” otherwise “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")
Conclusion
Hope you could understand how to use NOT function in Google Sheets. Similarly, you can use NOT with AND. But as I told you at the beginning of this post, in most of the cases you can simply use<> comparison operator or reversing AND to OR or OR to AND. NOT is always not a must.
