How many grades are required for IFS?

IFS function

The IF function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE CONDITION. IFS can be used as a replacement for many nested IF statements and is easier to read when multiple conditions are used.

Simple syntax

In general, the syntax for the function is

IFS: = IFS ([something is true1; value if true1; something is true2; value if true2; something is true3; value if true3)

Note that you can use the IFS function to test up to 127 different conditions. However, it is not recommended to nest too many conditions with IF or IFS statements. This is because multiple conditions must be entered in the correct order and can be very difficult to create, test, and update.

syntax

  • IFS (logic test1; value_if_true1; [logic test2; value_if_true2]; [logic test3; value_if_true3]; ...)

argument

description

Logic test 1 (required)

Condition that evaluates to TRUE or FALSE.

Value_if_true1 (required)

Result that is returned when LogicTest1 evaluates to TRUE. Can be empty.

Logic test 2… Logic test 127 (optional)

Condition that evaluates to TRUE or FALSE.

Value_if_true2… value_if_true127 (optional)

Result that is returned when Logic testN is evaluated as TRUE. Every argument Value_if_trueN corresponds to a condition of Logic testN. Can be empty.

example 1

The formula for cells A2: A6 is:

  •  = IFS (A2> 89; "A"; A2> 79; "B"; A2> 69; "C"; A2> 59; "D"; TRUE; "F")

This means: IF (A2 is greater than 89, then return an "A"; IF A2 is greater than 79, then return a "B", etc .; return an "F" for all other values ​​less than 59).

Example 2

The formula in cell G7 is:

  •  = IFS (F2 = 1; D2; F2 = 2; D3; F2 = 3; D4; F2 = 4; D5; F2 = 5; D6; F2 = 6; D7; F2 = 7; D8)

This means: IF (the value in cell F2 is 1, then return the value in cell D2; IF the value in cell F2 is 2, then return the value in cell D3, etc .; finally end with the value in cell D8 if none of the other conditions is met).

Hints

  • To provide a default result, enter TRUE for your final logic test argument. If none of the other conditions are met, the appropriate value is returned. In example 1, this is illustrated in lines 6 and 7 (with grade 58).

  •  If the argument Logic test without an associated Value_if_trueArgument is given, this function displays the error message "You entered too few arguments for this function".

  •  If a logical_test is evaluated and resolved to a value other than TRUE or FALSE, this function returns a value #VALUE! displayed.

  •  If no conditions of the type "TRUE" are found, the function returns the error "#NV!" back.

Do you need more help?

You can always ask an expert in the Excel Tech Community, get support from the Answers Community, or suggest a new feature or improvement on Excel User Voice.

Related topics

IF
Advanced IF functions - work with nested formulas and avoid pitfalls
Training Videos: Advanced What-If Features
The COUNTIF function counts values ​​based on a single criterion.
The COUNTIFS function counts values ​​based on several criteria.
The SUMIF function sums values ​​based on a single criterion.
The SUMIFS function sums values ​​based on several criteria
AND
OR
VLOOKUP
Overview of formulas in Excel
Avoiding broken formulas
Recognizing errors in formulas
Logical functions
Excel functions (alphabetical)
Excel functions (by category)