New functions of Excel 2016

With the new release of Excel 2016, six new very interesting functions have been added in three libraries.

Library Text

Function CONCAT

  • Combines the text from multiple ranges and/or strings, but it doesn’t provide the delimiter or ignore empty arguments
  • This function depreciates CONCATENATE

Function TEXTJOIN

  • Better than the CONCAT function (it’s strange that Microsoft has introduced these 2 functions in the same release)
  • TEXTJOIN combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined.
  • If the delimiter is an empty text string, this function will effectively concatenate the ranges
Function TEXTJOIN
TEXTJOIN with space delimiter

Library Logical

Function IFS

  • Can take the place of multiple nested IF statements, and is much easier to read with multiple conditions. 
  • The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition

Function SWITCH

  • The SWITCH function evaluates one expression (a logical test) against a list of values, and returns the result corresponding to the first matching value.
  • This function is similar to the function SWITCH in Python, DAX, …
  • If there is no match, an optional default value may be returned.

Library Statistical

Function MAXIFS

MAXIFS returns the maximum value among cells specified by a given set of conditions or criteria

Function MAXIFS
Return the maximum in function of the product name

Function MINIFS

MINIFS returns the minimum value among cells specified by a given set of conditions or criteria

Leave a Reply

Your email address will not be published.