How to Use the IFS Function in Excel

The IFS function in Excel and Google Sheets is a logical function designed to evaluate multiple conditions sequentially and return a value corresponding to the first condition that is true. It is particularly effective when dealing with several potential conditions, each leading to a different outcome.

Basic Syntax:

The basic syntax for the IFS function is as follows:

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

Examples:

Here are some practical examples to illustrate how the IFS function operates:

Example 1 – Grading System:

Imagine a grading system with these criteria:

  • Score < 50: Fail
  • 50 <= Score < 70: Pass
  • 70 <= Score < 90: Good
  • Score >= 90: Excellent

To allocate grades based on the student’s score, you can use the IFS function as shown:

Score Grade
65 =IFS(A2<50, “Fail”, A2<70, “Pass”, A2<90, “Good”, A2>=90, “Excellent”)

Example 2 – Categorizing Sales Data:

Next, consider categorizing sales data into segments:

  • Sales < 1000: Low
  • 1000 <= Sales < 5000: Medium
  • Sales >= 5000: High

The IFS function can be applied to classify each sales figure into the appropriate category:

Sales Category
3500 =IFS(A2<1000, “Low”, A2<5000, “Medium”, A2>=5000, “High”)

These examples clearly show how the IFS function can efficiently process multiple conditions to yield the relevant results for each scenario.

More information: https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45

Other functions
Returns TRUE if all of its arguments are TRUE
Returns the logical value FALSE
Specifies a logical test to perform
Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression
Reverses the logic of its argument
Returns TRUE if any argument is TRUE
Evaluates an expression against a list of values and returns the result corresponding to the first matching value If there is no match, an optional default value may be returned
Returns the logical value TRUE
Returns a logical exclusive OR of all arguments