Business Analytics
Data Analyst
Programming for Data Science
Predictive Analytics for Business
Are you a data enthusiast? Join us for a ride on your data and automate your stuffs

EXCEL FUNCTION – IFS

 

Microsoft Excel “IFS Function” is a Logical Function and provides output as per the many logical situations. “IFS Function” is very easy to understand and to apply, it comes with unique feature of multiple logical conditions, we can place one or more logical cases and can get the output as per the requirement.

“IFS Function” provides the output if applicable conditions are “TRUE”.
Function statement consist of all the conditions which are “TRUE”, and it is necessary to provide the conditions in correct sequence or order (i.e. “Descending order” (if applicable)) to avoid the function error or incorrect output.

 

Advantage of IFS Function:

“IFS Function” can be used in multiple areas that makes the function very useful. Sometimes when we work on the large database it is very difficult to apply and fill the logical output manually (one by one) and “IFS Function” helps to make the work easy, saves time and increases efficiency.

Most of the time, “IFS Function” is used for conditions on “NUMBER” values however this function can be used with “TEXT” string such as TEXT Equals to, Contains etc.

 

Where “IFS Function” can be used:

“IFS Function” provides many choices of condition that makes this function to be used in multiple situations. Like it can be used as follows:

– Grading of Marks for Schools/Colleges/Institutions (i.e. if marks obtained are greater the 90% then Grade should be “A” or if marks obtained are greater than 80% then Grade should be “B” and so on)

– Sales Performance Tracker (i.e. if sales of an item are greater than 10 then Remark should be “FIRST” or if sales of an item is greater than 5 then Remark should be “SECOND” and so on.

– Feedback Summary (if Rating of any product is 5 than Feedback should be “Excellent” or if Rating of any product is 4 than Feedback should be “GOOD” and so on)

– TEXT Lookup Conditions (if City is “DELHI” then Country should be “INDIA” or if City is “NEWYORK” then Country should be “USA” and so on)

– Likewise, whenever there are multiple conditions and need to make output accordingly then “IFS Function” can be used

Syntax:

=IFS(logical_test1,[value_if_true1], …)

Syntax Description:

logical_test1, argument is used to give logical conditions, like if A is equals to (A=), if A is greater than (A>) or less then (A<) etc.

[value_if_true1], argument is output argument and provides result in-case statement or logical condition is TRUE.

…, we can give the multiple conditions in sequence based on the above logical syntax.

 

Things to Remember:

We need to ensure that the logical conditions that we are providing are in sequence or in order. Here correct sequence means, suppose we need to place three conditions like, if A>10 then output should be “FIRST” and if A>5 then output should be “SECOND” and if A2>2 then output should be “THIRD”. In this case A>10 condition should be place first to get the appropriate result and so on.

In the above case, suppose we have value i.e. 11, which is greater than 10, 5 and 2 then what should be the output? (FIRST, SECOND or THIRD?). if we follow the correct descending order (i.e. A>10 as first condition) then output would be “FIRST”

If any condition is not meeting the criteria, then the output will be shown as #N/A

Example 1: Grading of Marks for Schools/Colleges/Institutions:

Suppose there is requirement of preparation of Grade Summary for marks obtained. For example, if marks obtained are greater the 90% then Grade should be “A” or if marks obtained are greater than 80% then Grade should be “B” and so on.

Syntax:  =IFS(A2>90,"A",A2>80,"B",A2>60,"C",A2>50,"D",A2>40,"E")
IFS Function

Example 2: Sales Performance Tracker

We can also prepare “Sales Performance Tracker” with the help of “IFS Function”, for example if sale of items is greater than 10 then Remark should be “FIRST” or if sales of items is greater than 5 then Remark should be “SECOND” and so on.

Syntax: =IFS(A2>10,"FIRST",A2>5,"SECOND",A2>1,"THIRD")
IFS Function

Example 3: Feedback Summary

Feedback Summary can also be prepared like, if Rating of any product is 5 than Feedback should be “Excellent” or if Rating of any product is 4 than Feedback should be “GOOD” and so on.

Syntax:   =IFS(A2=1,"WORST",A2=2,"POOR",A2=3,"AVERAGE",A2=4,"GOOD",A2=5,"EXCELLENT")
IFS Function

Example 4: TEXT Lookup Conditions

“IFS Function” can also be used for TEXT Lookup Conditions like, if City is “DELHI” then Country should be “INDIA” or if City is “NEWYORK” then Country should be “USA” and so on.

Syntax:  =IFS(A2="DELHI","INDIA",A2="NEWYORK","USA",A2="LONDON","UK",A2="DUBAI","UAE")
IFS Function

Hope you liked. Happy Learning.

Don’t forget to leave your valuable comments!

Leave a Reply

Your email address will not be published. Required fields are marked *