BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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:
The feminist ratings are fairly subjective ...
The IFS function has this syntax:
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:
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:
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:
B2,
"Drippy","Avoid at all costs",
"No idea","Read reviews",
"Feisty","Recommended",
"A must-see film")
This would give:
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:
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:
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:
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!