What is the IFS function in Excel?
→ The MS Excel IFS function helps us to test multiple conditions in a single function cell.
→ IFERROR Function checks about the different errors like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL.
→ The IFS function is the new method for nesting the if function.
→ Also, You can visit here: the regular Nested If condition in Excel with Example
→ The IFS Function is a very easy and quick method for nesting if function.
→ One drawback of this function is that we can not mention the ELSE condition with this function.
→ The IFS function is a built-in function in Excel
→ We can put this function into the Logical Function Category.
How to use the IFS function in Excel?
→ This function can be entered as part of a formula in a cell of a worksheet.
What is the return value of the IFS function?
→ IFS function returns 0 (zero) if we do not mention anything in the syntax of the IFS function.
→ For better understanding we will refer to the different examples that are mentioned below.
What is the Syntax of IFS function?
→ The syntax of IFS function in Microsoft Excel is:
=IFS(Condition1, Value1, [Condition2, Value2],…[Condition127, Value127])
The Syntax Parameter or Syntax Arguments are:
→ Condition1 – The first condition that is checked.
→ Value1 – The value to return if the first condition is TRUE.
→ [Condition2….Condition127] – These are optional arguments, and we can use up to 127 conditions in the IFS function.
→ [Value2….Value127] – These are optional arguments, and we can use up to 127 values.
→ One condition has one corresponding value and the value would be returned when the corresponding condition will be true.
👉 Notes:
→ The IFS function would return the value of the first TRUE condition only.
→ So if we apply multiple conditions and we have multiple conditions are true the function will return only the first true condition.
→ All the conditions in the IFS function must return either TRUE or FALSE.
→ If all the conditions in the IFS function return FALSE, then the result of the IFS formula would be the #N/A error which means the value is unavailable.
Examples of IFS Function
→ This function is used when we need to check multiple conditions in a sequence and return the value when the first condition is true.
→ Like we can use this for defining the students' grades or calculating the commission based on sales.
→ We will use the IFS condition to calculate the student's grades based on their score on the exam.
→ From the below picture we have one table that contains grades and marks that is in the E and F columns.
→ As per the IFS function's syntax =IFS(Condition1, Value1, [Condition2, Value2],…[Condition127, Value127]) we will apply the formula that is mentioned below.
→ We will apply the formula =IFS(B2<35,F2,B2<51,F3,B2<71,F4,B2<86,F5,B2<100,F6)
→ So it will return the grade as per the obtained marks in the exam.
Example-02: Commission Calculation based on Sales
→ Now we will use this function for commission calculation
→ From the below picture we have one table that shows the commission percentage based on the sale value.
→ As per the IFS function's syntax =IFS(Condition1, Value1, [Condition2, Value2],…[Condition127, Value127]) we will apply the formula that is mentioned below.
→ We will apply the formula =IFS(B2<E3,F2,B2<E4,F3,B2<E5,F4,B2<E6,F5,B2>E6,F6)*B2
→ So it will return the commission based on the sale
👉 For a regular update:
➨ Join us (Telegram Channel)
➨ Join us (LinkedIn Page)
👉 Also Navigate Our Popular Category:
➨ Lookup & Reference Functions
Post a Comment