IFS is Microsoft’s stab at trying to shorten those incredibly long nested IF THEN ELSE statements where we need to perform a bunch of logical tests. Classic example would be coding out the logic to change a number value to a letter grades (90-100 = A, 80-89 = B, etc). Of course that can also be achieved via VLOOKUP or INDEX/MATCH, but the common way I see people do it is with nested IF logic.
Excel eliminates the mess of parenthesis and ELSE logic with the IFS function. Giving out grades would look something like:
=IFS(A2>89,“A”,A2>79,“B”,A2>69,“C”,A2>“59”,D, TRUE, “F”)
What would need adding is logic to capture the possibility of none of those conditions being true and that is what the TRUE captures
SWITCH
The new SWITCH function handles the situation where you are comparing several different values to the same expression. Microsoft has their own good explanation here. Similar results probably could also be achieved using a VLOOKUP or INDEX/MATCH – but hey, it’s nice to have options.
The IFS() function checks one or more conditions and returns a value that corresponds to the first TRUE condition—that only sounds confusing. This function uses the following syntax:
IFS(condition1, truevalue1, [condition2, truevalue2], ...)
The conditionx expression must evaluate to TRUE or FALSE and truevaluex is returned when conditionx is TRUE. If you enter a conditionx value, you must enter a truevaluex value. This format is straightforward and easy to interpret and update.
This function can eliminate nested IF() functions, which are handy and effective, but messy and difficult to maintain. The IFS() function shown in Figure C is much easier to write and update than its nested IF() counterpart.
SWITCH() is similar to IFS(), but with a subtle difference: Instead of specifying conditional expressions, you specify an expression and a series of values and results. You're looking for an exact match; when the first exact match is found, its corresponding result is returned. The function also accommodates a default to return when no match is found.
The syntax is simple:
SWITCH(expression, value1, result1, [default or value2, result2] ...)
where expression is the value being compared to valuex and resultx is the value returned when the first two match.
Figure D shows the following SWITCH() function returning a shortened code for each region:
=SWITCH(D2,"Southeast","SE","Northeast","NE","Central","C","Southwest","SW","Southwest","SW","No listing")
The options are straightforward and it's easy to see that there's no Northwest region. This is a good error clue for you. On close examination, you can see that I used Southwest; SW twice; simply change one to Northwest; NW to correct the expression. This type of error is easy to make. For this reason, I recommend always including a default argument, even if you think one isn't necessary.
Whadya mean, Excel's SWITCH function??? It doesn't exist, it does now. You hav'nt missed it you just didn't realise that you really needed it all these years.
IFS and SWITCH functions help specify a series of conditions
The new IFS and SWITCH functions give you an alternative to using a series of nested IF functions, like “IF(IF(IF()))”, when you have more than one condition that you want to test to find a corresponding result. The IF function is one of the most commonly used functions in Excel, and using IF inside IF (nested IF functions) has been a common practice in Excel, but it can be challenging or confusing at times.
The advantage of using the new IFS functions is that you can specify a series of conditions in a single function. Each condition is followed by the result that will be used if the condition is true—making it very straightforward to create and read the formula afterward. For example, let’s say you want to get the grade letter for a given score on a test. Using the IFS function, it might be something like this:
=IFS(C1>=90, “A”, C1>=80, “B”, C1>= 70, “C”, C1>=60, “D”, C1,"Fail")
<60 ail="" br="">
<60 ail="" br="">
This can be read as, if the grade in C1 is greater than or equal to 90, it’s an A. Otherwise, if it’s greater than or equal to 80, it’s a B. Otherwise, if it’s greater than or equal to 70, it’s a C and so on. It’s pretty easy to write it this way and it’s also straightforward to read and understand what’s going on.
The SWITCH function also handles multiple conditions. What makes it different is that rather than specifying a series of conditional statements, you specify an expression and a series of values and results. The values are compared to the expression, and when the first exact match is found, the corresponding result is applied to the cell. You can also specify a “default” result that will be returned if none of the values are an exact match for the expression. The advantage of the SWITCH function is that you can avoid repeating the expression over and over, which sometimes happens in nested IF formulas.
In the example below, the first part of the formula extracts the size code (i.e. XS, M and G) from the middle of the item in column B. It’s rather long, so it’s nice that SWITCH only needs it to be written once and it can be compared to a list of values.
The example below can be explained as:
Extract the size code from the item in column B. If it equals “XS”, the result is “Extra Small.” Otherwise, if it equals “S”, the result is “Small” and so on. If there’s no match, the result is “Not Specified.”
<60 ail="" br="">
The same result could be calculated using nested IF functions, but it would be significantly longer, as shown below.
Excel 2016 SWITCH function - the compact form of nested IF
If you ever spent far too much time, trying to get a nested IF formula, you'll like using the freshly released SWITCH function in Excel. It can be a real timesaver in situations where complex nested IF is needed. Earlier available only in VBA, SWITCH has been recently added as function in Excel 2016, Excel Online and Mobile, Excel for Android tablets and phones.
Excel SWITCH - syntax
The SWITCH function compares an expression against a list of values and returns the result according to the first matching value. If no match found, it's possible to return a default value which is optional.
The structure of the SWITCH function is as follows:
SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])
It has 4 arguments one of which is optional:
Expression is the required argument compared against value1…value126.
ValueN is a value compared against expression.
ResultN is the value returned when the corresponding valueN argument matches the expression. It must be specified for each valueN argument.
Default is the value returned if no matches have been found in the valueN expressions. This argument doesn't have a corresponding resultN expression and must be the final argument in the function.
Since functions are limited to 254 arguments, you can use up to 126 pairs of value and result arguments.
The SWITCH function vs. nested IF in Excel with use cases
The Excel SWITCH function, as well as IF, helps specify a series of conditions. However, with this function you define an expression and a sequence of values and results, not a number of conditional statements. What is good with the SWITCH function is that you don't need to repeat the expression again and again, which sometimes happens in nested IF formulas.
While everything is ok with nesting IFs, there are cases where the numbers of conditions for evaluation make building a nested IF irrational.
To demonstrate this point, let's have a look at the use cases below.
Say, you have several acronyms and you want to return the full names for them:
DR - Duplicate Remover
MTW - Merge Tables Wizard
CR - Combine Rows.v The SWITCH function in Excel 2016 will be quite straightforward for this task.
Use the Excel Switch function to return full names for acronyms
With the IF function you need to repeat the expression, so it takes more time to enter and looks longer.
Return full names for acronyms using nested If in Excel
The same can be seen in the following example with the rating system where the Excel SWITCH function looks more compact.
Return values for rating scores with the switch function
Return values for rating scores with the If function
Let's see how SWITCH works in combination with other functions. Suppose, we have a number of dates and want to see at a glance if they refer to today, tomorrow, or yesterday. For this we add the TODAY function that returns the serial number of the current date, and DAYS that returns the number of days between two dates.
You can see that SWITCH works perfectly for this task.
Return values for dates using Excel SWITCH
With the IF function, the conversion needs some nesting and gets complex. So the chances of making an error are high.
Return values for dates using nexted Ifs in Excel
Being underused and underestimated, Excel SWITCH is a really helpful function that lets you build conditional splitting logic.
IFS function
Applies To: Excel 2016 , Excel Online , Excel for Android tablets , Excel Mobile , More...
The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.
NOTE: It's generally not advisable to use too many conditions with IF or IFS statements, as multiple conditions need to be entered in the correct order, and can be very difficult to build, test and update.
Remember: IFS is a new function that is only available in Excel 2016 including Excel Online, Mobile, Android phones and tables. If you open a workbook with the IFS function in an earlier version of Excel, all cells containing the IFS function will display a #NAME? error.
Simple syntax
IFS([Something is True1, Value if True1, [Something is True2, Value if True2],…[Something is True127, Value if True127])
NOTES: The IFS function allows you to test up to 127 different conditions.
For example:
=IFS(A1=1,1,A1=2,2,A1=3,3)
Which says IF(A1 equals 1, then display 1, IF A1 equals 2, then display 2, or else if A1 equals 3, then display 3).
Technical details Example 1
IFS function Grades example. Formula in cell B2 is =IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F") The formula for cells A2:A6 is:
=IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F")
Which says IF(A2 is Greater Than 89, then return a "A", IF A2 is Greater Than 79, then return a "B", and so on and for all other values less than 59, return an "F").
Example 2
IFS function - Days of the Week example - Formula in cell G2 is =IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)
The formula in cell G7 is:
=IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)
Which says IF(the value in cell F2 equals 1, then return the value in cell D2, IF the value in cell F2 equals 2, then return the value in cell D3, and so on, finally ending with the value in cell D8 if none of the other conditions are met).
Remarks
To specify a default result, enter a condition that will always be true for your final logical_test argument, such as TRUE or 1=1. If none of the other conditions are met the corresponding value will be returned. In Example 1, rows 6 and 7 (with the 58 grade) demonstrate this.
If a logical_test argument is supplied without a corresponding value_if_true, this function shows a "You've entered too few arguments for this function" error message.
If a logical_test argument is evaluated and resolves to a value other than TRUE or FALSE, this function returns a #VALUE! error.
If no TRUE conditions are found, this function returns #N/A error.
No comments:
Post a Comment