IFS Function

Friends,

I hope you are well known with IF function, the most amazing logical function in Excel. Excel is improving itself day by day; it’s getting intelligent to provide its best service as per our requirement.

I assume and sure that you have used the nested IF function as, =IF(conditional checking (giving reference to certain cell), value if True, IF((conditional checking, value if True, (conditional checking, value if True, ….. and so on.

Now, this is how nested IF works – a condition inside a condition, inside a condition and inside a condition. If the first condition is true, then the following conditions will be ignored. If the first condition is false, then the next condition is waiting in the [value if false]. Like this, you can ask a series of conditions to get to the correct answer.


We use Nested IF to get the desired data logically; for example, if this thing proves to be wrong then do this, and so on.

Today we will discuss about such a function which can reduce your time as well as key-stroking in your PC or Laptop and reduces the chances of making mistakes also it is faster than IF function - the function is IFS.

The IFS function is built in such a manner that it just goes on checking for the first true result and only then calculate. This makes it much faster to implement.

Description

The Microsoft Excel IFS function lets you specify multiple IF conditions within one function call.

A function call means how many times you are writing  the function.

Syntax

The syntax for the IFS function in Microsoft Excel is:

=IFS(logical test 1, value_if_true 1 [,logical test 2, value_if_true 2] ... [,logical test 127, value_if_true 127] )

Parameters or Arguments

logical test 1, logical test 2, ... logical test 127

The condition that you want to test. There can be up to 127 
Conditions entered

value_if_true 1, value_if_true 2, ... value_if_true 127


The value that is returned if the corresponding condition is TRUE. 

All conditions are evaluated in the order that they are listed so once the function finds a condition that evaluates to TRUE, the IFS function will return the corresponding value and stop processing any further conditions.

Note : The maximum number of logical test can be performed by IFS function is 127.

Returns

The IFS function returns any datatype such as a string, numeric, date, etc.


Note : If none of the conditions evaluate to TRUE, the IFS function will return the #N/A error.

Difference between IF and IFS

To understand the difference between IF and IFS function, please follow the below image carefully.

Difference between IF and IFS function in Excel

Let us analyze the steps we do normally in case of nested IF function or multiple IF function.

Steps :-

1. We first determine what is our conditional checking part.

2. Next we type our first IF statement with a bracket

3. We write our condition and then either on the TRUE part or in the FALSE part we type the second IF statement and if this condition is returned to TRUE or FALSE then we put another IF function and so on.

Basically, these are the steps we follow while framing multiple IF function or nested IF function. Depending upon the output of IF and IFS function we can say there is NO DIFFERENCE in both these functions. In syntax, we will get difference.

The most important feature in IFS function is that you don’t need to write multiple IF function one after another. It reduces keystrokes and it is faster.

It only works on the TRUE part of the IF function, if the first condition is FALSE, it will stop.

If every output of IFS function returns FALSE, IFS function will return #N/A error.

Example :

How to use IFS function

In the below image, I have plotted 9 students’ name and the marks obtained in Subject 1, let suppose we need to derive the GRADE as per marks obtained by each student.

The marks required to obtain GRADE is give in the side of this image.

If I use IF function the formula becomes =IF(C10>=85,"A",IF(C10>=75,"B",IF(C10>=65,"C",IF(C10>=50,"D"))))

Here I have nested IF function with another IF function, the IF functions other than first one is based on the FALSE statement/output.

Whereas, when I use IFS function, the Excel IFS formula becomes
=IFS(C10>=85,"A",C10>=75,"B",C10>=65,"C",C10>=50,"D") on the same condition or criteria.

You can easily differentiate between the two function the IF and the IFS.

How to use IFS function

I think you have enjoied this article about IFS function.
Thank you for reading...