To use the NOT Function in Google Sheets
Basic Usage of NOT Function:
- The syntax of the NOT function is simple: =NOT(logical_expression), it simply inverses the logical expression.
Advanced Way to use the NOT Function:
- Imagine you have a list of students and their ages.
- You want to determine who can go on a school trip, and you’ve decided that only students aged 18 or older can go.
- In an empty cell next to a student’s age, use the formula: =NOT(age < 18).
- This formula checks if the age is less than 18 and negates the result, allowing you to find out if a student can go on the trip.
Hi. In this article, we will learn a pretty useful NOT function. We will learn how to use NOT function in google sheets.
NOT function is simply an inverting function that can negate a logical expression or value at once. Simple is that, if I pass a true into the NOT function that it will return TRUE, and if I pass true it will return FALSE. It’s very simple, you might be thinking why do we need to use this function? What’s the practical usage of this function, and why and when can we be needed to negate a value of the logical expression? I understand your concerns let me discuss how and when NOT Function helps us work with logical expressions mostly the Boolean values such as True and False.
Use Case of NOT Function in Google Sheets
We many times see some situations even in the real life when we have to declare the true as false and the false as true. Let’s understand with an example, let’s say a shopping mall has several products and has several deals on some specific products, now here they mainly use the NOT function real-life example, the sales manager will say if the sale of a specific product is more than 100 then remove the deal from it to save the product going out of stock, similarly, the other condition is that if the sale of a specific product is less than 20 then start the deal from it to save the product going unsold.
This is how it works in real life, now sales manager is simply doing the thing that is done by the NOT function, not function checks some condition, and based on that it flips the result into true or false, if (condition) = true then flip the Boolean value true or false. This is the best real-life example of NOT function. We will learn it in the context of google sheets so you will understand it better. Therefore we need to learn how to use NOT Function in google sheets.
How to use NOT Function in Google Sheets
Not function is pretty simple, it is simple but the conditions and logical operations used along with NOT function can be complex, we will learn how to deal with them and we will try to understand the functionality and features of NOT function.
How to use NOT Function in Google Sheets – Basic Usage
NOT function Syntax
=NOT(logical_expression)
Here, the logical expression can be anything from if-else, AND, OR, etc.
The basic usage of the NOT function is to inverse a Boolean or logical expression.
Step 1
Have a Boolean value in a cell
Step 2
In any adjacent cell, try =NOT(cell_address), to see the NOT effect.
Step 3
This is how NOT simply inverse the value, TRUE to FALSE and FALSE to TRUE.
But this does not make any sense. Using a NOT function like this does not look useful. We will see some real use cases by which you will see how handy NOT function can be.
Let’s move to some use cases.
How to use NOT Function in Google Sheets – A Simple Use case
In this section, we will see a simple use case of the NOT function, it’s a real-life application of the NOT function and you will find some interest to use the NOT function after learning and seeing this use case.
Here I have some students and their ages. Now I want to check each person’s age and based on age I will decide who is going on a school trip.
A student’s age must be equal to greater than 18 to go on a school trip.
Step 1
Sample data
Step 2
In the third column, use a condition =NOT(B4<18)
The above formula will check the value in cell B4 and compare it with 18, it will check if B4 is less than 18, if true it will return false, and if false it will return true. This is how NOT can inverse any result
Step 3
The result.
Step 4
Now you further need to check if students went on the school trip last year, where the condition is simple, if students are going then they must not have gone last year so we need inverse answers in the next column.
Step 5
Simply you can use the syntax =NOT(C4)
You can say, this can be done using IF, but the syntax of IF is pretty long, and this is not the best of NOT.
The if Syntax for the same purpose
=IF(B4>=18,”True”,”False”)
This is how we can use the NOT function to shorten the syntax and get efficient results.
How to use NOT Function in Google Sheets – With IF Condition
Here in this section, we will learn how to use the NOT function in google sheets with if condition. If the condition is written above, it can also do the same operations as NOT but with long syntax. Let’s see how we can use NOT with IF in google sheets.
For this use case, I have another dummy data set to work on.
I have some products and yesterday sold units. I need to calculate if I should open some kind of offers on products for today, I will determine it looking at yesterday’s sale. If yesterday’s sale is less than 50 then I will open some offer on this product or if the product’s yesterday’s sale yesterday was 50 then I will close the sale because it’s already an average selling product. I hope you have understood the scenario.
Let’s move to practical implementation
Step 1
Sample data
Step 2
Keep “Offer Yesterday” true for every product.
Step 3
We need to determine “Offer Today”
Step 4
Write the IF condition in the first row of the “Offer Today” column
Step 5
The overall formula will be
=IF(B4<50,C4,NOT(C4))
Explanation
If the value in the B4 is less than 50, then simply write what’s written in C4, and if not, then return NOT(C4), which means the inverse of the C4.
Step 6
This is how we will get the accurate results
In another use Case of IF with NOT function, we will calculate the rewards for the workers who worked on weekends
For this, I have another data set
Step 1
Sample data
Step 2
Add the formula in the last column in which you want to calculate the reward
Step 3
We are also using the ISBLANK function to check if the cell is blank.
Step 4
The formula will be
=IF(NOT(ISBLANK(L5)),L5*100,”-“)
Step 5
The result
This is how you can do this very simply with the combination of ISBLANK, NOT, and IF
How to use NOT Function in Google Sheets – With Conditional Formatting
In this section, we will learn how to use NOT Function in Google Sheets with conditional formatting. We have a sample data set for it. Normally we specify a condition in the conditional formatting and according to the custom formula, the formatting is applied to our filtered content. What if I want to reverse this logic? Yes, we can use the NOT function here.
For this example, I have a dataset on which we will apply this logic practically
I have a dataset with the student’s names and their cities. I want to apply conditional formatting where the city is not “Las Vegas”. Hare, if I don’t use NOT Function, so I will have to check all other conditions other than LAs Vegas, so you can understand how complicated it will become without NOT Function. Since we are smart so we will use NOT Function.
Step 1
Sample data
Step 2
Select All (without headers)
Step 3
Go to Format > Conditional Formatting
Step 4
Click on the drop-down icon below the “Format cells if…” section.
Step 5
Select “Custom formula is”
Step 6
Write the condition
=NOT($S5=”Las Vegas”)
Step 7
Choose the formatting from below “Formatting Style” section
Step 8
Click on Done
This is how you can make it very easy to apply to negate conditional formatting based on a condition.
Conditional formatting is one of the most common ways that use the NOT function to make negative conditions and save a lot of time and efforts
Note: NOT Function can also be used with logical operator functions like AND and OR to negate after checking two conditions or checking either one of the two conditions.
Important Notes on NOT Function in Google Sheets
- NOT Function simply negates the logical value or expression
- NOT can be used with any other function or logical expression such as IF, AND, etc
- NOT is a function and it can also be used under conditional formatting custom formula
- NOT Function can also be used with OR/AND functions
Download/Copy Google Sheets Practice Workbook
Note: Do not ask for editors’ access. Simply make a copy and use the template.
Conclusion
Wrapping up how to use NOT function in google sheets. we have learned how to use the NOT function in google sheets, we have seen several use cases of using the NOT Function along with IF and conditional formatting. We learned real-life examples like making an offer looking at yesterday’s sale, and formatting the values where the city is not Las Vegas. These conditional logics are very easy with the NOT function if we use other ways to perform this logic, we may have to cover a longer syntax, and also the margin of error will be increased. NOT Function makes it very easy to negate any logical value and expression easily in one step.
I hope you find this article helpful. I will see you next time. Take care, and keep learning with Office Demy.