Director SumProduct
Share this content
Tags:

What’s new in Excel 2019: Functions

14th Dec 2018
Director SumProduct
Share this content
spreadsheet
istock_anyaberkut_ss

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.

1

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

2

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:

3

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:

4

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.

5

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.

Tags:

Replies (3)

Please login or register to join the discussion.

avatar
By Cantona1
14th Dec 2018 15:00

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.

Thanks (0)
avatar
By steve tees
14th Dec 2018 16:20

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

Thanks (0)
avatar
By the_fishmonger
20th Dec 2018 16:28

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?

Thanks (0)