Excel is a powerful tool for handling data and performing complex calculations. One of the many functions available in Excel is the IFS function, which allows you to test multiple conditions and return a value based on the outcome. In this article, we’ll explore how to use the IFS function in Excel and some examples to help you understand it better.

How to Use the IFS Function

In simple terms, the IFS function tests one or more conditions and returns a value based on the outcome. To use this function, you’ll need to know the criteria you want to test and what value you want to assign to them.

The basic syntax of the IFS function is:

=IFS(condition1, value1, [condition2, value2],…)

Condition1: this is the first condition to test.

Value1: the value to return if the condition1 is true.

Condition2: this is the next condition to test. You can have up to 127 different conditions.

Value2: the corresponding value to return if condition2 is true.

To use this function in Excel, follow these steps:

1. Open a new or existing workbook in Excel.
2. Navigate to the cell where you want to use the IFS function.
3. Enter the function in the cell by typing =IFS and then start typing the first condition. For example, =IFS(A1>=90,”A”).
4. Continue adding more conditions and values separated by commas until you have included all the criteria you want the formula to test.

Examples of the IFS Function in Excel

Let’s look at two examples of how to use the IFS function in Excel.

Example 1

Suppose you want to categorize the grades of students in a class based on the marks they scored on a test. In this case, you can use the IFS function to assign grades to each mark, such as A, B, C, D or F.

Here’s how you can use the IFS function to categorize the grades:

=IFS(A1>=90, “A”, A1>=80, “B”, A1>=70, “C”, A1>=60, “D”, A1<60, “F”)

In this example, if a student’s mark in cell A1 is above or equal to 90, the formula returns “A”. If the mark is between 80 and 89, the formula returns “B”, and so on. If the student’s mark is less than 60, the formula returns “F”.

Example 2

Suppose you want to calculate the commission of sales representatives based on their sales figures. In this case, you can use the IFS function to assign a commission rate to each sales figure and then multiply the sales figure by the commission rate to get the commission amount.

Here’s how you can use the IFS function to calculate the commission:

=IFS(A1>=10000, 0.1, A1>=5000, 0.05, A1<5000, 0)

In this example, if a sales representative’s sales figures in cell A1 are above or equal to $10,000, the formula returns a commission rate of 0.1 (10%). If the sales figures are between $5,000 and $9,999, the formula returns a commission rate of 0.05 (5%), and if the sales figures are less than $5,000, the formula returns a commission rate of 0.

Finally, to calculate the commission amount, you can multiply the sales figure by the commission rate, as shown below:

=A1*IFS(A1>=10000, 0.1, A1>=5000, 0.05, A1<5000, 0)