The new functions in Excel 2016, including IFS, SWITCH and TEXTJOIN
Excel 2016 has a few interesting new functions! TEXTJOIN allows you to concatenate all the cells in a range, and IFS and SWITCH provide other ways to make choices.

Posted by Andy Brown on 18 February 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

New Functions in Excel 2016

Normally new versions of Excel include functions which are (to put it kindly) of niche interest, but Excel 2016 includes some gems - as you'll see if you read on.

Should you want to try out any of the formula given on this page, you're very welcome to download this workbook of Disney Princesses to try!

You can see a summary of all of the new features in Excel 2016 here.

The IFS Function

Suppose that you want to arbitrarily divide Disney princesses into 3 eras:

Year Era
Up to 1980 Baby boomer
1980-2000 Generation X
2000 onwards Millennial

Prior to Excel 2016, you'd have to use this nested IF function:

=IF(
  C2"1980,"Baby boomer",
  IF(C2<2000,"Generation X","Millennial")
)

Putting this formula in for the top princess below and copying it down would give:

Nested IF

The feminist ratings are fairly subjective ...

The IFS function has this syntax:

=IFS(
  first condition, what to do if it's true,
  second condition, what to do if it's true, ...)

So for the first Disney princess above, the formula could read:

=IFS(
  C2<1980,"Baby boomer",
  C2<2000,"Generation X",
  TRUE,"Millennial")

So the advantage is that you don't have to repeat the IF function, but the disadvantage is that you have to remember to mop up at the end (in this case, provide a description for princesses not born before 1980 and not born before 2000 either).

In any case, if you find yourself writing functions like this you should almost certainly be using the VLOOKUP function or MATCH/INDEX functions instead.

The SWITCH function

This has been part of other Microsoft products for years, but has finally made it into Excel.  The syntax is:

=SWITCH(
    What you're testing,
    First value, Value if it matches, ...
    Second value, Value if it matches, ...
    Optional default value if not found a match so far)

For our example, we could choose to expand on our feminist rating to give a verdict for each film, using the following table:

Feminist rating Film verdict
Drippy Avoid at all costs
No idea Read reviews
Feisty Recommended
Anything else (ie Role model) A must-see film

We could expand on our feminist rating with the following function:

=SWITCH(
   B2,
    "Drippy","Avoid at all costs",
    "No idea","Read reviews",
    "Feisty","Recommended",
    "A must-see film")

This would give:

Feminist verdicts

This worked, but it would have been much easier to do with a VLOOKUP or MATCH/INDEX function. I can't think of any reason to use SWITCH instead!

 

The TEXTJOIN Function

This is my favourite new function in Excel 2016, and is like the JOIN or JOINLINES function found in other MS applications.  Suppose that you want to get a list of the princesses, and the year each first appeared:

Joining princess names

Assume that the block of princesses has been given range name Princesses, and the first appearance years have been given the range name Appeared.

The TEXTJOIN function takes three arguments:

Argument What to put
1 The delimiter (the text dividing up the entries)
2 Whether to ignore empty cells
3 The range of cells whose values you want to join together

Here's what our answer should look like:

Princesses concatenated

The formula should join all the values in the two ranges together.

The two functions are simple:

What Function
Princesses =TEXTJOIN(", ",TRUE,Princesses)
Appearance years =TEXTJOIN(", ", TRUE,Appeared)

In each case we've elected to ignore empty cells, although since there aren't any it wouldn't have made any difference what we put for the second argument.

The CONCAT Function

Microsoft have introduced the CONCAT function to replace the longer CONCATENATE function (although the latter will still be supported).  So the following 3 formula would all give Wise Owl Training:

Three concatenation formulae

These formulae would all return the same thing!

Which does make you wonder why Microsoft bothered ... 

MAXIFS and MINIFS

And finally ... if you've used SUMIFS, AVERAGEIFS and COUNTIFS to do conditional statistics, and wondered where MAXIFS and MINIFS were - they're now part of Excel!

This blog has 0 threads Add post