Purpose

Return value

Syntax

=IFS(test1,value1,[test2, value2],...)
  • test1 - First logical test.
  • value1 - Result when test1 is TRUE.
  • test2, value2 - [optional] Second test/value pair.

Using the IFS function

The IFs function evaluates multiple expressions and returns a result that corresponds to the first TRUE result. You can use the IFS function when you want a self-contained formula to test multiple conditions at the same time without nesting multiple IF statements. Formulas based on IFS are shorter and easier to read and write.

Conditions are provided to the IFS function as test/value pairs, and IFS can handle up to 127 conditions. Each test represents a logical test that returns TRUE or FALSE, and the value that follows will be returned when the test returns TRUE. In the event that more than one condition returns TRUE, the value corresponding to the first TRUE result is returned. For this reason, it is important to consider the order in which conditions appear.

Structure

An IFS formula with 3 tests can be visualized like this:

=IFS(
test1,value1 // pair 1
test2,value2 // pair 2
test3,value3 // pair 3
)

A value is returned by IFS only when the previous test returns TRUE, and the first test to return TRUE “wins”. For better readability, you can add line breaks to an IFS formula as shown above.

Note: the IFS function does not provide an argument for a default value. See Example #3 below for a workaround.

Example #1 - grades, lowest to highest

In the example shown below, the IFS function is used to assign a grade based on a score. The formula in E5, copied down, is:

=IFS(C5<60,"F",C5<70,"D",C5<80,"C",C5<90,"B",C5>=90,"A")

Notice the conditions are entered “in order” to test lower scores first. The grade associated with the first test to return TRUE is returned.

Example #2 - rating, highest to lowest

In a simple rating system, a score of 3 or greater is “Good”, a score between 2 and 3 is “Average”, and anything below 2 is “Poor”. To assign these values with IFS, three conditions are used:

=IFS(A1>=3,"Good",A1>=2,"Average",A1<2,"Poor")

Notice in this case, conditions are arranged to test higher values first.

Example #3 - default value

The IFS function does not have a built-in default value to use when all conditions are FALSE. However, to provide a default value, you can enter TRUE as a final test, followed by a value to use as a default.

In the example below, a status code of 100 is “OK”, a code of 200 is “Warning”, and a code of 300 is “Error”. Any other code value is invalid, so TRUE is provided as the final test, and “Invalid” is provided as a “default” value.

=IFS(A1=100,"OK",A1=200,"Warning",A1=300,"Error",TRUE,"Invalid")

When the value in A1 is 100, 200, or 300, IFS will return the messages shown above. When A1 contains any other value (including when A1 is empty) IFS will return “Invalid”. Without this final condition, IFS will return #N/A when a code is not recognized.

IFS and performance

You might expect the IFS function to “short-circuit” and stop evaluating once it finds a logical test that returns TRUE, but this is not the case. IFS evaluates all conditions and expressions , even when the first logical test is TRUE. This behavior can degrade performance when an IFS formula includes complex or time-consuming calculations, since those calculations may still run even when their conditions are FALSE. It can also cause problems in recursive LAMBDA functions, because the “else” branch is evaluated even when it should be bypassed, potentially triggering unintended recursion and a #NUM! error.

To avoid these issues, consider rewriting the formula with nested IF functions or using the simpler CHOOSE function . Both IF and CHOOSE perform true short-circuit evaluation, skipping unnecessary calculations once a valid result is found.

IFS versus SWITCH

Like the SWITCH function , the IFS function allows you to test more than one condition in a single self-contained formula. Both functions make it easier to write (and read) a formula with many conditions. One advantage of SWITCH over IFS is that the expression appears just once in the function and does not need to be repeated. In addition, SWITCH can accept a default value. However, SWITCH is limited to exact matching. It is not possible to use operators like greater than (>) or less than (<) with the standard syntax. In contrast, the IFS function requires expressions for each condition, so you can use logical operators as needed.

Notes

  • The IFS function does not have a built-in default value to use when all conditions are FALSE.
  • To provide a default value, enter TRUE as a final test, and a value to return when no other conditions are met.
  • All logical tests must return TRUE or FALSE. Any other result will cause IFS to return a #VALUE! error.
  • If no logical tests return TRUE, IFS will return the #N/A error.
  • IFS does not have short-circuit behavior; all expressions are evaluated

Purpose

Return value

Syntax

=NOT(logical)
  • logical - A value or logical expression that can be evaluated as TRUE or FALSE.

Using the NOT function

The NOT function returns the opposite of a given logical or Boolean value. Use the NOT function to reverse a Boolean value or the result of a logical expression . When given FALSE, NOT returns TRUE. When given TRUE, NOT returns FALSE. The NOT function is commonly used with other functions like IF, AND, and OR to create complex logical tests.

NOT function basics

The purpose of the NOT function is to reverse a Boolean value, for example:

=NOT(TRUE) // returns FALSE
=NOT(FALSE) // returns TRUE

NOT is often used to reverse the result from another function. For example, if cell A1 contains “purple”, the formula below will return FALSE since A1 is neither “green” nor “red”:

=OR(A1="green",A1="red") // returns FALSE

If we want to reverse this logic, we can wrap the OR function inside the NOT function:

=NOT(OR(A1="green",A1="red")) // returns TRUE

The OR function returns FALSE (as before), and the NOT function returns TRUE.

Example - NOT this OR that

In the worksheet below, the goal is to test each color in column B and return TRUE if the color is not “green” or “red”. The formula in C5, copied down, is:

=NOT(OR(B5="green",B5="red"))

<img loading=“lazy” src=“https://exceljet.net/sites/default/files/styles/original_with_watermark/public/images/functions/inline/not_function_not_this_or_that.png" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“The NOT function with OR - not “red” or “green” - 1”>

The literal translation of this formula is “NOT green or red”. At each row, the formula returns TRUE if the color in column B is not green or red, and FALSE if the color is green or red.

Example - IF + NOT

The NOT function is often used inside the IF function as a logical test. For example, in the worksheet below, the goal is to “flag” colors that are not “red” or “green” with an “x”. The formula in cell C5 looks like this:

=IF(NOT(OR(B5="red",B5="green")),"x","")

<img loading=“lazy” src=“https://exceljet.net/sites/default/files/styles/original_with_watermark/public/images/functions/inline/not_function_if_not_this_or_that.png" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“The NOT function with IF - if not “red” or “green” - 2”>

As the formula is copied down, it returns an “x” if the color in column B is NOT “green” or “red”. Otherwise, the formula returns an empty string (””).

Example - Not blank

A common use case for the NOT function is to reverse the behavior of another function. For example, If cell A1 is blank (empty), the ISBLANK function will return TRUE:

=ISBLANK(A1)  // TRUE if A1 is empty

To reverse this behavior, wrap the NOT function around the ISBLANK function:

=NOT(ISBLANK(A1))  // TRUE if A1 is NOT empty

By adding NOT the output from ISBLANK is reversed. This formula will return TRUE when A1 is not empty and FALSE when A1 is empty. You might use this kind of test to only run a calculation if there is a value in A1:

=IF(NOT(ISBLANK(A1)),B1/A1,"")

Translation: if A1 is not blank, divide B1 by A1, otherwise return an empty string (””). This is an example of nesting one function inside another.