## 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:

- IFS
- SWITCH
- CONCAT
- TEXTJOIN
- MAXIFS
- MINIFS

Let’s go through them.

### IFS

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

**=IF(IF(IF…**

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:

**IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…)**

where:

**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"))))**

Lovely.

### SWITCH

**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])**

where:

**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

**MID($G13,SEARCH("-",$G13)+1,SEARCH("-",$G13,(SEARCH("-",$G13)+1))-SEARCH("-",$G13)-1)**

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)="XS","Extra Small",IF(MID($G13,SEARCH("-",$G13)+1,SEARCH("-",$G13,(SEARCH("-",$G13)+1))-SEARCH("-",$G13)-1)="S","Small",IF(MID($G13,SEARCH("-",$G13)+1,SEARCH("-",$G13,(SEARCH("-",$G13)+1))-SEARCH("-",$G13)-1)="M","Medium",IF(MID($G13,SEARCH("-",$G13)+1,SEARCH("-",$G13,(SEARCH("-",$G13)+1))-SEARCH("-",$G13)-1)="L","Large",IF(MID($G13,SEARCH("-",$G13)+1,SEARCH("-",$G13,(SEARCH("-",$G13)+1))-SEARCH("-",$G13)-1)="XL","Extra Large","Not Specified")))))**

### CONCAT

This function replaces the **CONCATENATE** function (see this article about concatenation). The **CONCAT** function combines the text from multiple ranges and / or text strings:

**CONCAT(text1, [text2],…)**

where:

**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.

### TEXTJOIN

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], …)**

where:

**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!

**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:

**{=MAX(IF(G13:G31=H34,IF(H13:H31=H35,IF(I13:I31=H36,J13:J31))))}**

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.

## Replies

## Please login or register to join the discussion.

On an excel forum, I have read the dreadful Array keys(CRTL+ALT+ENTER) will be replaced by a new easy function. If this is true, it will revolutionise excel.

I'm a bit confused here. I have Excel 2016 (Office Home & Business v 1811) and all these are existing 2016 functions...

Is 2019 also known as Version 1811 (Build 11029) in the 365 programme?

The latest upgrade might have added some of the above but, since that happened:

a) Excel crashes if you try to "Print Preview Full screen" and

b) if you want to use the formula "=sum(A1:Z10)" to cover that range by typing =sum(A1. and moving the cursor to cover the rest of the range, Excel no longer recognises that it should change the full-stop to a semi-colon and does not Sum the range at all. Instead you have a cell with "#FIELD!".

The latter could simply be an option that they've changed to default the other way but I've not found it yet!

Why is it that most updates/upgrades seem to break something useful more often these days?

## Please login or register to join the discussion.