IFS Function in Google Sheet

  • Reading time:3 mins read
  • Post category:Functions
  • Post comments:0 Comments

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

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:-

  1. condition1 :- First condition to be tested by the function
  2. value1 :- value returned when condition1 is TRUE
  3. condition2 :- Second condition to be tested by the function
  4. 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:

  1. Conditions can be constructed using any of the comparison operators like <,>,<=,>=, <> etc
  2. All conditions must returns a TRUE or FALSE. If not the case then #N/A Error will pop-up
  3. 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.
  4. 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")
Example of using IFS Function

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")
Weight Category Assignment from BMI

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%")
Example of IFS  Function

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 🙂

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

Liked the content?? Then share the word!!!

Rishav

I love Google Sheets and heavily rely upon it on a day to day basis. SheetsInfo is my attempt to share my learning with all of you. Hope you find the articles easy to read and simple to understand.

Leave a Reply