SI.CONDITIONS(IFS)

What it does

IFS replaces a chain of IF functions nested inside each other. Write the tests from the strictest to the loosest: Excel stops at the first one that is TRUE.

Syntax

=SI.CONDITIONS(logical_test1; value_if_true1; [logical_test2; value_if_true2]; …)

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ;

Arguments

logical_test

A test that gives TRUE or FALSE.

value_if_true

What to return if this is the first true test.

Example

Grades: 90 or more is A, 80 or more is B, 70 or more is C, the rest is D. What does a score of 84 get?

The formula in B2:

=SI.CONDITIONS(A2>=90;"A";A2>=80;"B";A2>=70;"C";VRAI;"D")

Result: B

84>=90 is FALSE, so Excel moves on. 84>=80 is TRUE, so the answer is B and the later tests are never checked. The last test, TRUE, catches every score below 70.

Common mistakes

• Tests in the wrong order. If 70 or more comes first, a score of 95 stops there and gets C.
• No catch-all at the end. When no test is TRUE, IFS returns #N/A; end with TRUE and a default result.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• IFS: many conditions in orderModule 4 · Logic

More from this module

• SI
• SI.MULTIPLE
• ET
• OU
• NON
• OUX
• ESTNUM
• ESTTEXTE
• ESTVIDE
• ESTERREUR
• SIERREUR

All functions