Tuesday, May 30, 2017

The New IFS Function

Once in a while, Microsoft comes up with a new feature in Excel that thoroughly captures my interest. As most Office 365 users know, there are new features and functions being regularly added to this latest version of Excel. Not all of them strike the fancy (as the British may say…) of a large group, but there are exceptions.

One such marvelous feature (IMHO) is the new IFS function. If you are like many Excel users, you have had experience with Nested IF Functions.  These handy functions offer a wonderfully powerful way to extract information from your data subject to multiple conditions. 

Regrettably, however, Nested IF Functions tend to be rather, well, Clunky. Thankfully, the new IFS function simplifies the nested practice, as it allows you to specify a series of conditions in a single Streamlined Function!

Instead of being tasked with stringing multiple IF functions into a mega-function, (which will likely boggle the minds of others who might inherit your work), you can achieve the same results in a much more elegant format.

Let’s use the classic exam grading system of assignments for an illustration. Assume that you have a student’s numerical grade in Cell A1.  Using our cool new function, we can construct a formula in Cell B1 as follows:

=IFS(A1>=90, “A”, A1>=80, “B”, A1>= 70, “C”, A1>=60, “D”, A1>=0, "More studying is recommended…”)

If the student’s score in A1 is 84, then the clear result in B1 will be “B”.  This marvelous enhancement to Excel that can save you a good deal of time, and make your formulas easier understood by others as well as yourself. If you are an Office 365 user, try it sometime. I’m confident that you will find this a very worthy addition.

No comments: