What is NOT Function in Google Sheets?
The NOT function in Google Sheets returns the reverse of the logical value of a given expression. For instance, NOT(TRUE) returns FALSE, and NOT(FALSE) returns TRUE. The NOT function takes only a single argument, which is a cell reference containing a logical value or a logical expression and returns the opposite of that value. Hence, if the argument evaluates to TRUE, the NOT function will return FALSE.
It is one of the logic functions available in Google Sheets along with the likes of AND and OR. For example, let us type the following function in Google Sheets and check the result =NOT(10<5). The result is TRUE as 10<5 is FALSE and the NOT function returns the reverse of this value. Let us look at more examples in the examples section to understand how the NOT function works in Google Sheets.
Key Takeaways
- The NOT function in Google Sheets takes a logical argument and reverses its value. For instance, if we supply TRUE as an argument, it returns FALSE and vice versa.
- The basic syntax for the NOT function is:
- =NOT(logical_expression)
- One can combine NOT with other logical functions like AND and OR to create more complex conditions.
- We consider an empty cell a FALSE value.
Syntax
To get the optimum advantage of the NOT function, it is often combined with other logical functions such as IF, AND, and OR. Its syntax is straightforward.
=NOT(logical_expression)
Here, the logical_expression can be a cell reference or an expression returning a logical value, TRUE or FALSE.
The NOT formula in Google Sheets checks for only logical values like TRUE or FALSE. When using it with other data types, we may obtain unexpected results.
How to use NOT Function in Google Sheets?
The NOT function is a pre-defined function in Google Sheets. We use the NOT function to reverse the outcome of a logical test or Boolean value. One can enter it in two ways in Google Sheets.
- Enter NOT manually
- Through the Google Menu bar
Entering the NOT Function manually
We have a sample table with some logical values or expressions evaluating to logical values. Let us use the NOT function on them manually and check the outcome. Here’s how you can do that with NOT.
Step 1: Here, let us enter some relevant logical values and expressions in Column A.
Step 2: To find the NOT values, input the function by entering =NOT(, followed by the cell reference containing the values. Close the braces.
Step 3: Press Enter to execute the formula. Grab the fill handle up to B3 to manually get the results for the entire column.
Using the Google Menu bar
We can enter the NOT function through the Google menu bar in the following way.
- Choose the cell where you must enter the formula.
- Go to the menu bar and click on “Insert” ➝ “Function” ➝ Logical ➝ “NOT.”
Once the function is entered in Google Sheets, enter the required argument, which is a logical value or a cell reference containing a logical value and press Enter.
Examples
One often uses the NOT function in conjunction with other functions to simplify complex logical tests, especially with AND and OR.
Example #1
We can use the NOT function in Google Sheets along with the IF function to check if a value is below 40 or not and display an output based on it. For example, we have some details in the table below. It contains the grades of some students from cells B2 to B8.
1) In Column A, we have their names.
2) In Column B, we enter the grades of each of the students.
3) In Column C, we write the student conduct.
4) In Column D, based on this result, we apply the formula and get the result
Let us proceed with the steps as shown below.
Step 1: Look at the entries made in the table. Based on this, the output in Column D should be either “Passed” or “Failed.”
Check if the student is eligible to Pass or Fail using the following formula in cell D2.
=IF(AND(NOT(B2>40), C2=”Poor”),”Failed”,”Passed”).
Step 2: Press Enter to obtain the result for the first entry. Drag the formula down for all the values.
Thus, we use NOT along with the AND function to check for multiple conditions to arrive at a result.
Example #2 – NOT Function in Conditional Formatting
We use the NOT function in Google Sheets in conditional formatting based on meeting certain conditions. Let us look at the NOT in Google Sheets example below. We have some entries in a sheet. We want all the non-blank entries to appear in a different color. Let us look at the steps on how to go about implementing it.
Step 1: Below is a table with a list of random values.
To apply conditional formatting, go to Format and choose “Conditional Formatting.” You get a Conditional Format rules pane on the right.
Step 2: Here, as we have already selected the range, it appears in the pane. Go to the option “Format cells if.” Here, choose “Custom Formula Is.” In the box that appears, enter the following formula:
=NOT(ISBLANK(A1:E5)).
For the formatting options, choose a color of your choice.
Step 3: Click on the “Done” button and observe your table. Here, we have showcased all the non-blank text in a different color.
By flipping the values of TRUE to FALSE and FALSE to TRUE, NOT is helps in creating dynamic, conditional logic within formulas.
Example #3 – Nested NOT Functions
In this example, we have some sales details of a few individuals with their names and the products they sold. We have to find if the person “Sarah” has sold “Product B.” Let us see how to implement it using NOT.
- The person’s name is “Sarah”.
- The product is not “Product X or Product A”.
- We can use a nested NOT function to handle these conditions.
Step 1: In cell C2, you can use the following formula to determine the places where Sarah has sold Product B.
=IF(AND(NOT(B2=”Product A”), NOT(B2=”Product X”), A2=”Sarah”),”Sarah sold Product B”,”Invalid”).
Explanation:
- NOT(B2=”Product A”): B2 should not contain Product A.
- NOT(B2=”Product X”): B2 should not contain Product X.
- NOT(A2=”Sarah”): Ensures that the individual’s name is “Sarah.”
- AND(…): Combines the conditions. All conditions must be true for the value to be marked as “Sarah sold Product B”.
The IF function then outputs “Sarah sold Product B” if all conditions are true, otherwise “Invalid.”
Step 2: Press Enter and drag the formula till cell B7. For all the sales values where the name is “Sarah” and the product is “Product B,” we get the result as “Sarah sold Product B”. The nested NOT function allows you to “invert” the conditions, making the logic more flexible.
This nested approach can be used to handle complex exclusion conditions in your Google Sheets without needing to manually check each condition.
Important Things to Note
- If the argument to the NOT function in Google Sheets is given as 0, the output is “TRUE” as zero’s logic value is FALSE in Google Sheets. All other numeric values have a logic value of TRUE.
- If you input text or other non-numeric values, the NOT function returns #VALUE! error.
- While NOT is a straightforward function, its power lies in how it’s combined with other functions to create more complex logical structures.
- When using AND, OR, and NOT together, it is important to ensure their correct grouping to ensure the desired outcome.
The NOT function is very helpful to check if a certain condition is not met, than when it is.
Frequently Asked Questions (FAQs)
The NOT function is used to negate the result of a logical expression. Let us look at some scenarios where this would prove useful.
Inventory: You can manage inventory items by checking for items that are not in stock.
Student Performance evaluation: It helps identify students who have not passed a threshold score.
Conditional Formatting: It helps distinguish exceptions such as blank cells, certain text values, etc. in your dataset.
Error Handling: Helpful in error handling and data validation to flag cells that don’t meet certain conditions.
Task Management: Can be used to highlight tasks that are not completed.
You can use NOT and AND to exclude multiple conditions. It helps exclude a specific combination of conditions. For instance, =NOT(AND(A1>50, B1=”Passed”))
This formula will return TRUE if A1 is not greater than 50 and B1 is not “Passed.”
Similarly, you can use NOT and OR to avoid either of two scenarios. =NOT(OR(A1=”Red”, A2=”Green”))
Here, the output is TRUE only if neither A1 is Red nor A2 is Green.
If you use NOT on an empty cell, which is considered a FALSE value, you will get the result TRUE. If A1 contains a value, =NOT(A1) returns FALSE.
Download Template
This article must help understand NOT in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is NOT in Google Sheets. We learn its syntax & how to use it to reverse a logical value with examples & working template. You can learn more from the following articles. –
Leave a Reply