I. Intro
The IFS function is commonly used to determine whether a set of data meets one or more conditions.
II. About the function
- Formula: =IFS(condition 1, value 1, [condition 2, value 2], [condition 3, value 3]...)
- Arguments:
- Condition 1 (required): The first condition that is evaluated, which can be a logical value, a numerical value, an array, or a reference to those values.
- Value 1 (required): The response value when "Condition 1" is "TRUE".
- Condition 2 (optional): The second condition that is evaluated, which can be a logical value, a numerical value, an array, or a reference to those values.
- Value 2 (optional): The response value when "Condition 2" is "TRUE".
Note: You can add up to 128 different conditions and values for a total of 256 arguments. The formula must contain at least one pair of conditions and values. If multiple conditions are met, only the value of the first satisfied condition is returned. Enclose the values in double quotation marks (") to return them as text.
- Example:
- =IFS(B1>90, "Outstanding Result", B1>80, "Good Result")
- In this example, we are determining the score results in cell B1. If the score is greater than 90, the cell returns "Outstanding Result." If the score is greater than 80 but less than 90, it returns "Good Result."
III. Steps
Use the function
- Select a cell and click Formula in the toolbar, then select Logical > IFS. You can also directly enter =IFS in a cell.
- Enter the parameters in the cell. The formula in the GIF below: =IFS(C2>=20,"Yes",C2<20,"No")
- Press Enter.
- Drag the + symbol in the lower-right corner of the cell to apply the formula to more cells as necessary.
250px|700px|reset
Delete the function
Select the cell with the IFS function, and press Delete.
IV. Use cases
Teachers: Use IFS to assign grades
Teachers can easily convert scores into grades with the IFS function. For example, above 90 points is an A, above 75 points is a B, 60 points is a C, and 60 points or below is an F.
- Formula used below: =IFS(D2>90, "A", D2>75, "B", D2>60, "C", D2<=60, "F")
- About the parameters: In the GIF below, grades for Course C are assigned based on students' scores.
- D2>90 is condition 1, "A" represents value 1. This means that the resulting grade will be an A when the score is greater than 90.
- D2>75 is condition 2, "B" represents value 2. This means that the resulting grade will be a B when the score is greater than 75.
- D2>60 is condition 3, "C" represents value 3. This means that the resulting grade will be a C when the score is greater than 60.
- D2<=60 is condition 4, "F" represents value 4. This means that the resulting grade will be an F when the score is equal to or less than 60.
250px|700px|reset