|
<< Click to Display Table of Contents >> IFS (first condition met) |
Syntax:
IF(Condition1, Value1 [, Condition2, Value2 ...])
Description:
This function can test multiple conditions and returns the value of the first condition that is fulfilled. IFS allows shorter formulas than with a nested formula construction using the similar IF function.
Condition1 is the first tested condition and can be any expression.
Value1 is the result if Condition1 is fulfilled.
Condition2, Value2 (optional) is the second tested condition, etc.
As soon as the first fulfilled Condition is found, its corresponding Value is displayed as the result.
Note:
If none of the conditions is fulfilled, the #N/A error is returned.
Instead, you can use the condition TRUE at the end of the formula to specify a default value as the result, which is returned if none of the conditions is fulfilled (see example 2 below).
Compatibility notes:
Microsoft Excel supports this function only in version 2019 or later. In older versions, the function is unknown.
Example 1:
You have entered a sales volume in cell A1 and want to distribute a bonus according to the sales volume using the following distribution key:
Sales above 50000 = 2000 bonus; Sales above 25000 = 1000 bonus; Sales above 10000 = 500 bonus
The formula IFS(A1>50000, 2000, A1>25000, 1000, A1>10000, 500) then returns for example:
if the sales in cell A1 are 70000, the result is 2000 as a bonus.
if the sales in cell A1 are 30000, the result is 1000 as a bonus.
if the sales in cell A1 are 20000, the result is 500 as a bonus.
If cell A1 contains a sales volume of 9000, the formula returns the result #N/A, since none of the conditions is fulfilled. You could of course also specify a condition for this. Or you can specify instead, which default value the formula generally returns, if none of the conditions is fulfilled (see example 2).
Example 2:
You have the same initial situation as in example 1. At the end of the formula, use the condition TRUE to specify a default value that appears if none of the conditions is fulfilled:
IFS(A1>50000, 2000, A1>25000, 1000, A1>10000, 500, TRUE, "check")
For example, if cell A1 contains 9000 or -1000 as sales volume, the result is "check".
See also: