Using Excel IFS and SWITCH functions.

          Making Rent vs. Buy decisions always seemed like a no-brainer to me. If you hold on to your purchase long enough, you will be saving money: be it a car or a computer software. Case in point: it costs only $ 109.99 to purchase a license for Excel 2016 vs $69.99/year to lease Office 365 Personal, which includes Excel, as well as other Office products. Assuming you only need to use one product that truly matters, and you will use it for more than 19 months, buying is cheaper than renting. The longer you use the product without upgrading, the more money you save. This held true until February of this year, when Microsoft introduced 6 new functions available exclusively to Office 365 subscribers: TEXTJOIN, CONCAT, IFS, SWITCH, MAXIFS and MINIFS. This addition alone, coupled with introduction of Funnel charts might steer more users towards the subscription model. In this post we will review the IFS and SWITCH functions. Let’s say goodbye to nested if functions , we have already discussed on this blog.

          Whenever we need to accommodate for more than one outcome, using Nested IFs formulas =IF(IF(IF(IF()))) is very useful from the perspective of a patience-building exercise, otherwise the concept of the CASE logic seems to be a more natural fit here . While Microsoft supports the SELECT CASE statement in Excel’s VBA environment, as well as on its SQL Server platform, many Excel users added native support of this logic on their Excel wish list . Our voices were finally heard with the release of the IFS and SWITCH functions.

          To illustrate how the IFS() function works, let’s first go through a simple exercise of assigning a letter grade to students’ test scores. Following a typical GPA chart we would have the following:

          We will first use SELECT CASE functionality via user-defined VBA function (let’s call it LetterGrade ) to calculate letter grades as shown below:

          Translating GPA chart into VBA code, our function would look like so:

Function LetterGrade(TestScore As Integer)
'To Calcuate a letter grade for any given test score

    Select Case TestScore
          Case 0 To 59
              LetterGrade = "F"
          Case 60 To 62
              LetterGrade = "D-"
          Case 63 To 66
              LetterGrade = "D"
          Case 67 To 69
              LetterGrade = "D+"
          Case 70 To 72
              LetterGrade = "C-"
          Case 73 To 76
              LetterGrade = "C"
          Case 77 To 79
              LetterGrade = "C+"
          Case 80 To 82
              LetterGrade = "B-"
          Case 83 To 86
              LetterGrade = "B"
          Case 87 To 89
              LetterGrade = "B+"
          Case 90 To 92
              LetterGrade = "A-"
          Case 93 To 96
              LetterGrade = "A"
          Case 97 To 100
              LetterGrade = "A+"
          Case Else
              LetterGrade = "Unknown"
    End Select
    
End Function

          You don’t have to have any VBA programming background to follow logic powering this solution. Plugging in =LetterGrade(D4) formula into cell E4, Ammie’s test score of 74 is translated into letter grade C. Another way to solve this problem is through the nested IF functionality, we will use this formula:

=IF(D4>=97,"A+",IF(D4>92,"A",IF(D4>89,"A-",IF(D4>=87,"B+",IF(D4>82,"B",IF(D4>79,"B-",IF(D4>=77,"C+",IF(D4>72,"C",IF(D4>69,"C-",IF(D4>=67,"D+",IF(D4>62,"D",IF(D4>59,"D-", IF(D4<60,"F")             ))))))))))))

          Surely this solution beats having to incorporate VBA macros in your code, but it’s hardly easy to follow or maintain. This is where the new IFS function comes into play:
=IFS([Something is True1, Value if True1, …[Something is TrueN, Value if TrueN],)
In fact, the below formula is the closest Excel has ever come to using the CASE logic, simplifying our formulas by leaps and bounds:

=IFS(D4>92, 4, D4>89,3.7, D4>=87,3.3,D4>82, 3, D4>79,2.7, D4>=77,2.3, D4>72,2, D4>69,1.7, D4>=67,1.3, D4>62, 1, D4>59,0.7, D4<60, 0)

          One downside of this solution is the absence of catch all condition, that is readily available via nested IF functionality. You can however, use the TRUE condition, or perhaps incorporate IFERROR function to fill in the gaps. Speaking of eliminating ambiguity, SWITCH function comes equipped with a default argument to deal with unexpected inputs:
=SWITCH(expression, value1, result1,.. [default or valueN, resultN])

          SWITCH function is rather similar to IFS in a way it is finding a match for a certain condition, but avoids the need to specify a series of conditional statements. Suppose we want to assign description to different days of the week:

          Using the SWITCH function, we have the flexibility of flagging an invalid/unexpected input:

 =SWITCH(A2,"Monday","Happy Monday","Tuesday","Slump Day","Wednesday","Hump Day", "Thursday", "Almost Weekend", "Friday","TGIF","Saturday","Weekend","Sunday","Weekend","Invalid Day")

          Unfortunately, the IFS function solution does not allow for this flexibility:

=IFS(B4="Monday","Happy Monday",B4="Tuesday","Slump Day",B4="Wednesday","Hump Day",B4="Thursday","Almost Weekend",B4="Friday","TGIF",B4="Saturday","Weekend",B4="Sunday","Weekend")

          Bonus Solution to the Letter Grade problem is using almighty VLOOKUP function. If you use this function often enough you would probably agree that the default value set for the optional parameter of Range_lookup is not overly helpful. In fact, in most cases we are looking for an exact match rather than the closest one. Our current scenario is the perfect example why we would ever use the closest match. Assuming our lookup data range is sorted in ascending order, we will create a named range and call it LKP_GPA (CTRL + SHIFT + F3):

 =VLOOKUP(D4,LKP_GPA,3)

would provide the most elegant solution thus far, VBA macro non-withstanding…

          Feel free to download Excel workbook with the examples used in this post.

          Do you have any good examples on using either IFS or SWITCH functions?