This tutorial introduces the Excel IFS function, a streamlined alternative to nested IF statements. It simplifies creating formulas with multiple conditions and improves readability. Available in Excel 365, 2021, and 2019, IFS significantly reduces complexity compared to the older, more cumbersome nested IF approach.

Nested IF statements, while functional for multiple outcomes ("IF(IF(IF()))"), can become unwieldy and difficult to maintain. The IFS function offers a more elegant solution.

Understanding the Excel IFS Function

The IFS function evaluates a series of logical tests and returns the value associated with the first test that evaluates to TRUE. This makes it a direct and easier-to-understand replacement for multiple nested IF statements.

Syntax:

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2]...)

• logical_test1 (required): The first condition to evaluate (TRUE or FALSE).
• value_if_true1 (required): The value returned if logical_test1 is TRUE. Can be left blank.
• logical_test2...logical_test127 (optional): Subsequent conditions to evaluate.
• value_if_true2...value_if_true127 (optional): Values returned if corresponding logical_testN is TRUE. Can be left blank.

IFS supports up to 127 conditions. Insufficient arguments result in an error ("You've entered too few arguments for this function"), while invalid logical test results produce a #VALUE! error. If no condition evaluates to TRUE, the function returns #N/A.

IFS vs. Nested IF: Use Cases and Examples

The primary advantage of IFS is its concise structure. Multiple conditions and their corresponding results are clearly presented in a single function, enhancing readability and maintainability.

Example 1: Discount based on license count:

IFS:

=IFS(B2>50, 40, B2>40, 35, B2>30, 30, B2>20, 20, B2>10, 15, B2>5, 5, TRUE, 0)

Nested IF:

=IF(B2>50, 40, IF(B2>40, 35, IF(B2>30, 30, IF(B2>20, 20, IF(B2>10, 15, IF(B2>5, 5, 0))))))

Example 2: Formatting file sizes:

IFS:

=IFS(A2>=1024*1024*1024, TEXT(A2/(1024*1024*1024), "0.0") & " GB", A2>=1024*1024, TEXT(A2/(1024*1024), "0.0") & " Mb", A2>=1024, TEXT(A2/1024, "0.0") & " Kb", TRUE, TEXT(A2, "0") & " bytes")

Nested IF:

=IF(A2>=1024*1024*1024, TEXT(A2/(1024*1024*1024), "0.0") & " GB", IF(A2>=1024*1024, TEXT(A2/(1024*1024), "0.0") & " Mb", IF(A2>=1024, TEXT(A2/1024, "0.0") & " Kb", TEXT(A2, "0") & " bytes")))

The clarity and ease of updating the IFS function are evident when comparing it to its nested IF counterpart. For formulas with multiple conditions, the IFS function offers a significant improvement in both readability and maintainability.

The above is the detailed content of The new Excel IFS function instead of multiple IF. For more information, please follow other related articles on the PHP Chinese website!