How to use the NOT function in Google Sheets

There are scenarios when using Google Sheets where we may need a tool to negate a logical value or expression. The NOT function does exactly that. It returns FALSE if the input argument evaluates to TRUE and returns TRUE if the input argument evaluates to FALSE.

Usually, the NOT function is used in conjunction with other formulas such as IF, OR, or AND to build a comprehensive formula that can answer multiple conditions at the same time. This makes it a valuable formula to use in situations where you need to compare more than two conditions at once. For example, let’s say we’re calculating whether the sales team members are viable for a commission and bonus. We can use the NOT function to test whether they negate a certain income level or a certain number of accounts – if they provide a FALSE result for either one of the two, they can receive their commission. If they provide a FALSE result for both, they’re entitled to the bonus too.

Let’s explore the NOT function further and how you can use it in Google Sheets.

What is the NOT function?

The NOT function is quite a simple formula as there are only two possible variations.

Either it takes a logical TRUE as input and returns a FALSE, or it consumes a logical FALSE for input and returns a TRUE.

The formula also provides a response when we provide numbers as the input argument. It still returns a logical TRUE or FALSE regardless.

A zero is converted to logical FALSE and a non-zero a logical TRUE.

Syntax

The syntax for the NOT function is as follows:

• logical_expression: is an expression that evaluates to a logical TRUE or FALSE. This can be a direct expression or a reference to the cell that represents a logical expression

Please note: When using the NOT function on its own, it will only accept one input argument. This is because there is no way of negating two or more logical values together. They must be separated by something else.

How to use the NOT function in Google Sheets

On its own

Although the NOT function may not be that helpful in most situations when used on its own, it’s a good formula to use when determining a negative action.

For example, let’s say we’ve added a promotion to a product but users must have had an account for more than 1 year to be eligible. We can negate users with accounts less than a year old by obtaining a logical FALSE result with the following formula:

=NOT(B2<12)

As you can see, those who have had an account for more than 12 months are eligible for the promotion!

In conjunction with other formulas

The NOT function can also be used in conjunction with other formulas, such as OR, AND or IF. This allows us to compare multiple conditions at the same time in just one formula. If you’re not familiar with these other formulas, take a look at our other articles on spreadsheet functions such as How to use the nested IF statement.

Let’s take the example mentioned earlier and create a formula using the AND, OR, IF and NOT functions at the same time. If a salesperson exceeds the sales goal OR the account goal, they receive a commission. If a salesperson exceeds the sales goal AND the account goal, they get the bonus. The NOT function can be used to actually calculate the answers themselves, rather than just providing either a TRUE or FALSE return.

Firstly let’s determine which of the sales team members are eligible for commission using a formula containing IF,OR and NOT.

To determine which salespeople receives a commission, I must use the following formula:

=IF(OR(NOT(B11<$B$2),NOT(C11<$B$5)),B11*$B$3,0)

• If a salesperson has more than $12,500 in sales, they receive a 1.5% commission
• OR, if a salesperson has more than 10 accounts, they receive a 1.5% commission
• If they have neither, the value returns as 0.

Here are the results:

Now let’s determine which salespeople can receive a bonus. To do this, we need to incorporate the AND formula:

=IF(AND(NOT(B11<$B$2),NOT(C11< B5)),B6,0)

• If a salesperson has more than $12,500 in sales AND they have more than 10 accounts, they receive the $750 bonus
• If they do not fulfill both, the value returns as 0.

Here are the results:

As you can see here, only those who have both exceeded the sales AND account goals will receive their bonus.

NOT function in Google Sheets

And there you have it! The NOT function can be extremely helpful in negating logical values or expressions, especially when combined with other spreadsheet formulas.

If you would like to learn more about different formulas and functions in Google Sheets and also in Excel, take a look at our related blog posts below!