IFS Function is a unique offering of Google Sheet which helps us in applying multiple conditional checks at one go.
Normally to do the same with a IF Function, we would have to write a nested IF condition, which all of us know can be very messy at times.
In this tutorial we will deep dive into the working dynamics of the IFS Function and also compare it with IF Function to highlight the differences.
So, lets start
Table of Contents
Purpose
IFS Function helps to apply multiple test condition & return a value for the first TRUE Condition.
Syntax and Parameter Definition
Below is the Syntax of IFS Function:
=IFS(condition1, value1, condition2, value2, [condition3, value3] ...)Parameter Definition:-
- condition1 :- First condition to be tested by the function
- value1 :- value returned when condition1 is TRUE
- condition2 :- Second condition to be tested by the function
- value2:- value returned when condition2 is TRUE
In a similar way, you may add upto to a total of 127 [conditionX, valueX] argument pairs.
Key Points to Note:
- Conditions can be constructed using any of the comparison operators like <,>,<=,>=, <> etc
- All conditions must returns a TRUE or FALSE. If not the case then #N/A Error will pop-up
- During execution IFS Function will test all the conditions one by one and stop when the condition becomes TRUE. The value corresponding to the first TRUE condition is then returned.
- If none of the conditions return TRUE then the function returns a #N/A error as the output.
Expected Output
As mentioned, IFS Function will test multiple conditions and return the corresponding value of the first condition which is TRUE.
Below is a very simple example. We want to place 50, in the cell B5 in a suitable range.
To do this we use IFS Function and apply multiple condition over B5 to check it’s value.
=IFS(B5<10,"<10",B5<30,"10-30",B5<70,"30-70",B5<100,"70-100",B5>100,">=100")
As you can see from above , B5<70 is the condition which was eventually satisfied and hence the output if 30-70.
There are multiple other similar use-cases to IFS Function. Lets cover some of them in the upcoming section
Examples
Assigning Weight Category based on BMI
Say, we have BMI of 10 Adults and we want to assign the weight category basis it.
We will use the below formula to achieve our results.
=IFS(B5<10,"Underweight",B5<24.9,"Normal Weight",B5<29.9,"Overweight",B5>=29.9,"Obesity")
Estimating Discount Rate Based of Total Shopping Value
Say your store offers a different rate of disount based of the total shopping value.
Once Again we will take advantage of IFS Function to get the results.
=IFS(B5<1000, "0%",B5<2000,"2%",B5<4000,"3%",B5<6000,"4%", B5>6000, "5%")
Visual Demo of IFS Function
Before we end here is a sample visual demonstration of IFS Function in action.
That’s it on this topic. If you face any issues in implementing the above methods then leave a comment down below. Keep browsing SheetsInfo for more such useful information 🙂
