This guide covers everything you need to know about the Google Sheets NOT function, including its definition, syntax, use cases, and how to use it.
What is the NOT Function? How Does It Work?
The NOT function returns the opposite of a logical value. Essentially, the NOT function reverses the input logical value. If TRUE is the input, then FALSE is the output, and vice versa. This is illustrated as NOT(TRUE) returning FALSE and NOT(FALSE) returning TRUE.
Here’s an example: if you want to check if a cell, say A2, does not contain an error, you would use NOT(ISERR(A2)). This would return TRUE if A2 does not have an error and FALSE if it does.
Another important aspect to remember about the NOT function is its behavior with numeric values. In logical values, 0 is considered FALSE, and any non-zero numeric value is TRUE. Therefore, NOT(0) would return TRUE because the opposite of FALSE (0) is TRUE. Conversely, NOT(6) would return FALSE because the opposite of TRUE (6) is FALSE.
However, if non-numeric values are used as input, the NOT function will return an error #VALUE! This is because the function can only process logical values, not other data types.
NOT Syntax
The syntax and arguments for the function are as follows:
NOT(logical_expression)
In this syntax, there is only one argument:
- logical_expression: This is an expression or reference to a cell containing an expression that represents a logical value, that is, TRUE or FALSE.
Here are some important notes on the syntax and arguments of the NOT function in Google Sheets:
- The logical_expression argument is required for the function to work. If it’s omitted, the NOT function will return an error.
- The NOT function only works with logical values. If the logical_expression argument is not a logical value or does not resolve to a logical value, the function will return a #VALUE! error.
- The function reverses the logical value of the logical_expression. So, if logical_expression is TRUE, NOT(logical_expression) will return FALSE, and vice versa.
- In Google Sheets, 0 is considered FALSE, and any non-zero numeric value is considered TRUE. Therefore, NOT(0) will return TRUE, and NOT(any non-zero number) will return FALSE.
- If non-numeric values are input into NOT, the function will return the error #VALUE!. This is because NOT is a logical function and can only process logical values, not text or other non-numeric data types.
- The NOT function is not case-sensitive. So, NOT(TRUE) and NOT(true) will both return FALSE.
- The NOT function does not support array operations. If the logical_expression is an array or range of cells, the function will only consider the first cell in the range or array.
Examples of How to Use the NOT Function
Here are some practical examples of how to use the NOT function in Google Sheets.
Example #1: Basic Use of NOT Function
Suppose you have a logical value in cell A1 (TRUE or FALSE), and you want to reverse this value.
To do this, you simply need to use the NOT function. If A1 contains TRUE, then the formula =NOT(A1) will return FALSE. Conversely, if A1 contains FALSE, the formula will return TRUE.
Example #2: Using NOT with Comparison Operators
The NOT function can also be used with comparison operators. For instance, if you have two values in cells A2 and B2 and want to know if they are not equal. In this case, you can use the NOT function in conjunction with the equals operator.
The formula =NOT(A2=B2) will return TRUE if A2 does not equal B2 and FALSE if they are equal.
Example #4: Using NOT with Multiple Conditions
The NOT function can be used to reverse the result of multiple conditions. For instance, if you have values in cells A4, B4, and C4, and you want to check if none of these cells contain the number 5. You can use the OR function to check if any of the cells contain 5, and then use the NOT function to reverse the result.
The formula =NOT(OR(A4=5, B4=5, C4=5)) will return TRUE if none of the cells contain 5, and FALSE if at least one does.
NOT: Common Mistakes & Problems
When using the NOT function in Google Sheets, it’s important to avoid common mistakes and problems that can occur:
- Not understanding the binary nature of NOT: The NOT function only accepts boolean values, either TRUE or FALSE. You will receive an error if you try to use other data types, such as text or numbers.
- Neglecting to use parentheses: When using the NOT function, you must use parentheses around the logical expression you are negating. For example, NOT(A1>B1) is correct, but NOT A1>B1 is incorrect and will result in an error.
- Misusing the function with non-boolean values: While Google Sheets will try to interpret non-boolean values as TRUE or FALSE, it’s best to avoid this. For example, zero is interpreted as FALSE, and all other numbers are interpreted as TRUE. Text and error values are always interpreted as TRUE.
- Confusing the NOT function with the IF function: The NOT function simply reverses a TRUE or FALSE value. You should use the IF function instead if you want to perform different actions based on whether a condition is TRUE or FALSE.
- Over-complicating formulas with multiple NOT functions: While it’s possible to nest NOT functions, it can make your formulas complicated and difficult to understand. Instead, simplify your formulas by using other logical functions like AND, OR, or XOR.
- Ignoring case sensitivity: The NOT function is case sensitive. For example, NOT(“TRUE”) is not the same as NOT(“true”). The first will return an error, while the second will return FALSE. Always ensure that your logical expressions are in the correct case.
- Forgetting that the NOT function does not work with arrays: If you try to use the NOT function with an array, you will receive an error. The NOT function can only be used with single cell references or direct TRUE/FALSE values.
Why Is NOT Not Working? Troubleshooting Common Errors
If you’ve tried to use the NOT function in Google Sheets and it’s not working as expected, it’s possible that you’re encountering one of several common errors. This section will help you identify and troubleshoot these issues.
#VALUE! Error
Cause: The #VALUE! error is typically caused by incorrect data types. In the case of the NOT function, this error might appear if you’re trying to use a string or text value instead of a logical expression.
Solution: To fix this, ensure that the argument you’re passing to the NOT function is a logical expression. This means it should be something that can be evaluated as TRUE or FALSE. For example, instead of NOT(“text”), try NOT(A1 > B1), where A1 and B1 are cell references.
#N/A Error
Cause: The #N/A error appears when Google Sheets can’t find a value. In the context of the NOT function, this could happen if you’re referencing a cell that doesn’t exist or is currently empty.
Solution: Double-check the cell references in your formula to ensure they’re correct. If you’re trying to reference a cell that’s currently empty, consider using an IF or IFERROR function to handle this gracefully.
Formula Parse Error
Cause: A Formula Parse Error typically occurs when there’s a syntactical issue with your formula. This could be due to missing parentheses, incorrect use of operators, or other similar issues.
Solution: Review your formula carefully to ensure it’s structured correctly. The NOT function should be followed by an open parenthesis, a logical expression, and a closed parenthesis. For example, NOT(A1 > B1) is a valid use of the NOT function.
Circular Dependency Detected
Cause: The Circular Dependency Detected error occurs when a formula refers back to its own cell, either directly or indirectly. This creates an infinite loop that Google Sheets can’t resolve.
Solution: To fix this, you’ll need to revise your formula so it doesn’t depend on its own result. If you’re struggling to identify the circular reference, try using Google Sheets’ built-in error-checking tools, which can highlight the problematic cells.
Using NOT With Other Google Sheets Functions
Combining the NOT function with other Google Sheets functions can greatly enhance its utility and effectiveness. This section will explore how NOT can be used in conjunction with other functions like IF, AND, OR, and ISBLANK.
With IF
Usage: The IF function in Google Sheets is used to create conditional statements. When combined with the NOT function, it can create statements that execute if a certain condition is not met.
Example: Let’s assume we have a list of sales figures in column B, and we want to identify all sales that are not over $500. We would use the following formula:
=IF(NOT(B2>500), “Under 500”, “Over 500”)
This formula will return “Under 500” if the sales figure in B2 is not over $500, and “Over 500” otherwise.
With AND
Usage: The AND function checks if all given conditions are true. When combined with the NOT function, it can be used to check if all given conditions are not true.
Example: Assume we have a list of sales figures in column B and costs in column C, and we want to find out if the sales and costs are not over $500. We would use the following formula:
=AND(NOT(B2>500), NOT(C2>500))
This formula will return TRUE if both the sales figure in B2 and the cost in C2 are not over $500 and FALSE otherwise.
With OR
Usage: The OR function checks if at least one of the given conditions is true. Combined with the NOT function, it can be used to check if at least one of the given conditions is false.
Example: Assume we have a list of sales figures in column B and costs in column C, and we want to find out if the sales or costs are not over $500. We would use the following formula:
=OR(NOT(B2>500), NOT(C2>500))
This formula will return TRUE if the sales figure in B2 or the cost in C2 is not over $500 and FALSE otherwise.
With ISBLANK
Usage: The ISBLANK function checks if a cell is empty. When used with the NOT function, it can check if a cell is not empty.
Example: Assume we have a list of sales figures in column B, and we want to find out if there is a sales figure in B2. We would use the following formula:
=NOT(ISBLANK(B2))
This formula will return TRUE if there is a sales figure in B2 (i.e., the cell is not empty) and FALSE otherwise.
For more details on the NOT function, check out the official documentation at the Google Docs Editors Help Center.
