|
<< 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 are 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 are fulfilled (see example 2 below).
Compatibility notes:
Microsoft Excel supports this function only in version 2019 or later. The function is unknown in older versions.
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:
A sales volume of 70000 in cell A1 results in 2000 as a bonus.
A sales volume of 30000 in cell A1 results in 1000 as a bonus.
A sales volume of 20000 in cell A1 results in 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 are fulfilled. Of course, you could also set a condition for this scenario. Alternatively, you can specify instead the default value which the formula generally returns if none of the conditions are 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 are fulfilled:
IFS(A1>50000, 2000, A1>25000, 1000, A1>10000, 500, TRUE, "check")
If, for example, cell A1 contains a sales volume of 9000 or -1000, the result is "check".
See also: