Back in late September, Microsoft announced Office 2019 was now commercially available. In this new series, Liam Bastick has done some digging to see what is new compared to Excel 2016.
These functions have been in Office 365 for a while but there are now six new functions in Excel 2019 (compared to Excel 2016). These comprise:
Let’s go through them.
As model developers and reviewers, I must confess I remain unconvinced about this one. If you have ever used a formula with nested IF statements, eg
then maybe this function is for you – however, if you have ever written Excel formulae like this, then maybe Excel isn’t for you! There are usually better ways of writing the formula using CHOOSE or INDEX(MATCH) for example (see this article for more).
The syntax is as follows:
Content seriesView full content series
IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)
- logical_test1 is a logical condition that evaluates to TRUE or FALSE.
- value_if_true1 is the result to be returned if logical_test1 evaluates to TRUE; this may be empty.
- logical_test2 (and onwards) are further conditions that evaluate to TRUE or FALSE.
- value_if_true2 (and onwards) are the respective results to be returned if the corresponding logical_test evaluates to TRUE; any or all may be empty.
Since functions are limited to 254 arguments (sometimes known as parameters), the new IFS function can contain 127 pairs of conditions and results.
One thing to note is that IFS is not quite the same as IF. With the IF statement, the third argument corresponds to what do if the logical_test is not TRUE (ie it is an ELSE condition). IFS does not have an inherent ELSE condition, but it can be easily generated. All you have to do is make the final logical_test equal to a condition which is always true, such as TRUE or 1=1 (say).
Other issues to consider:
- Whilst the value_if_true may be empty, it must not be omitted. Having an odd number of arguments in an IFS statement would give rise to the “You’ve entered too few arguments for this function” error message.
- If a logical_test is not actually a logical test, eg it evaluates to something other than TRUE or FALSE, the function returns a #VALUE! error. Numbers still appear to work though: any number than zero evaluates as TRUE and zero is considered to be FALSE.
- If no TRUE conditions are found, this function returns the #N/A! error.
To show how it works, consider the following example.
Here, would-be gurus are graded based on evaluation criteria in the table, applied in a particular order:
=IFS(H13="Yes",I13,H14="Yes",I14,H15="Yes",I15,H16="Yes",I16,TRUE,"Not a Guru")
I think it’s safe that although it is reasonably straightforward to follow, it is entirely reasonable to say it’s not the prettiest, most elegant formula ever put to Excel paper. In particular, do pay heed to the final logical_test: TRUE. This ensures we have an ELSE condition as discussed above.
To be fair, one similar solution using legacy Excel functions isn’t any better:
=IF(H13="Yes",I13,IF(H14="Yes",I14,IF(H15="Yes",I15,IF(H16="Yes",I16,"Not a Guru"))))
SWITCH is already available in many alternative programming languages and can simplify potentially horrible formulae. This function evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. The syntax is as follows:
SWITCH(expression, value1, result1, [default or value2, result2],…[default or valueN, resultN])
- expression is the value that will be compared against the values (value1 to valueN) cited
- value1 to valueN are the values that will be compared against the expression
- result1 to result are the values, references or formulae results to be returned when the corresponding valueN argument matches the expression. The result must be supplied for each corresponding valueN argument
- default is an optional value to return in case no matches are found in the valueN expressions. The default argument is identified by having no corresponding result expression, i.e. it must be the final argument in the function where the function contains an odd, rather than an even, number of arguments. If no default argument is supplied and no match is found this function returns the #N/A! error.
To illustrate, consider the following painful formula:
=SWITCH(MID($G13,SEARCH("-",$G13)+1,SEARCH("-",$G13,(SEARCH("-",$G13)+1))-SEARCH("-",$G13)-1),"XS","Extra Small","S","Small","M","Medium","L","Large","XL","Extra Large","Not Specified")
The expression here is
which is determining what is contained between the two hyphens (for more on text string functions, please see this article). It is horrible, and that’s the point. The formula then considers what the values may be (“XL”,”M”) and what value should be returned as a consequence (“Extra Large”, “Medium”).
The corresponding Excel formula before SWITCH would have been a nightmare:
IF(MID($G13,SEARCH("-",$G13)+1,SEARCH("-",$G13,(SEARCH("-",$G13)+1))-SEARCH("-",$G13)-1)="XL","Extra Large","Not Specified")))))
This function replaces the CONCATENATE function (see this article about concatenation). The CONCAT function combines the text from multiple ranges and / or text strings:
- text1 is the text item to be joined
- text2 (onwards) are the additional items to be joined.
For example, consider the following illustration:
Upon first glance, CONCAT does the same thing as the legacy CONCATENATE function or & (ampersand) operator, but is just easier to spell. However, it is a little more than that: CONCATENATE will not cope with highlighting a contiguous range (it will just return the #VALUE! error); CONCAT will concatenate them all.
The TEXTJOIN function combines the text from multiple ranges and/or text strings and includes a delimiter to be specified between each text value to be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges similarly to the CONCAT function. Its syntax is
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
- delimiter is a text string (which may be empty) with characters contained within inverted commas (double quotes). If a number is supplied, it will be treated as text
- ignore_empty ignores empty cells if TRUE or the argument is unspecified (ie is blank)
- text1 is a text item to be joined
- text2 (onwards) are additional items to be joined up to a maximum of 252 arguments. If the resulting string contains more than 32,767 characters TEXTJOIN returns the #VALUE! error.
TEXTJOIN is more powerful than CONCAT. To highlight this, consider the following examples:
Here, in the formulae on rows 53 and 54, empty cells in a contiguous range may be ignored and delimiters only need to be specified once. When you compare to CONCAT, you do begin to wonder why you need it – perhaps to soften the demise of CONCATENATE?
MAXIFS and MINIFS
The last two new functions I am going to combine – and not with TEXTJOIN!
MAXIFS(max_range, criterion_range1, criterion1, [criterion_range2, criterion2], ...)
returns the maximum value among cells specified by a given set of conditions or criteria, where:
- max_range is the actual range of cells in which the maximum is to be determined
- criterion_range1 is the set of cells to evaluate with the criterion specified
- criterion1 is the criterion in the form of a number, expression or text that defines which cells will be evaluated as a maximum
- criterion_range2 (onwards) and criterion2 (onwards) are the additional ranges and their associated criteria. 126 range / criterion pairs may be specified. All ranges must have the same dimensions otherwise the function returns a #VALUE! error.
MINIFS behaves similarly but returns the minimum rather than the maximum value among cells specified by a given set of conditions or criteria.
This example is preferable to its standard Excel counterpart:
Array formulae (see this article for more information) are cumbersome and not readily understood.
Future articles in this series will explore the new charts and PivotTable updates of Excel 2019.
About Liam Bastick
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their strategic valuations team in London. He was also a senior member of the UK Post Office’s M&A and strategy teams and has worked for / assisted various other Australian modelling companies including BPM, Corality, Navigator Project Finance, PKF and SumProduct.
He has worked in the UK, Australia, Belgium, Denmark, France, Germany, Hong Kong, Indonesia, Malaysia, New Zealand, United States, Switzerland and Vietnam, with many internationally recognised clients, constructing and reviewing strategic, operational and valuation models for many high profile IPOs, LBOs and strategic assignments. Liam is a Fellow of the Institute of Chartered Accountants (ICAEW), a Fellow of the Institute of Chartered Management Accountants (CIMA) and is a professional mathematician.