Excel 2016 New Functions

In Excel 2016, Microsoft have introduced the following new built-in functions. Note that these functions are not available in Excel 2016 for Mac.

Text Functions
CONCAT

Joins together two or more text strings.

Concat Function Example

 ABC
1C:\Users\Jeff

In the above spreadsheet on the right, the text strings in cells A1, B1 and C1 can be joined together, using the Concat function, as follows:

=CONCAT( A1:C1 )

which gives the resulting text string C:\Users\Jeff


TEXTJOIN

Joins together two or more text strings, separated by a delimiter.

Textjoin Function Example

 ABC
1C:UsersJeff

In the above spreadsheet on the right, the text strings in cells A1, B1 and C1 can be joined together, separated by the delimiter "\", using the Textjoin function as follows:

=TEXTJOIN( "\", TRUE, A1:C1 )

which gives the resulting text string C:\Users\Jeff

Logical Functions
IFS

Tests a number of supplied conditions and returns a result corresponding to the first condition that evaluates to TRUE.

Ifs Function Example

 ABC
12002

In the above spreadsheet on the right, the ifs function can be used to divide the value in cell A1 by the first non-zero value in cells B1-C1:

=IFS( B1<>0, A1/B1, C1<>0, A1/C1 )

which gives the result 10.

(I.e. the value in cell A1 divided by the value in cell C1.)

SWITCH

Compares a number of supplied values to a supplied test expression and returns a result corresponding to the first value that matches the test expression.

Switch Function Example

 AB
12 

In the above spreadsheet on the right, the switch function is used to return a season relating to the number in cell A1.

=SWITCH( A1, 1, "Spring", 2, "Summer", 3, "Autumn", 4, "Winter" )

which gives the result "Summer".

Statistical Function
FORECAST.ETS

Uses an exponential smoothing algorithm to predict a future value on a timeline, based on a series of existing values

Forecast.Ets Function Example

If you have a series of monthly earnings, the Forecast.Ets function can be used to predict the earnings for the next month, as shown in the following spreadsheet:

Example of use of the Excel Forecast.Ets Function
FORECAST.ETS.CONFINT

Returns a confidence interval for a forecast value at a specified target date.

Forecast.Ets.Confint Function Example

The spreadsheet below shows a series of monthly earnings between Aug-2016 & Apr-2017, and the Forecast.Ets function has been used in cell G11, to predict the earnings for May 2017.

The Forecast.Ets.Confint function is used in cell C13 of the spreadsheet, to calculate a confindence interval for the forecasted value:

Example of use of the Excel Forecast.Ets.Confint Function
FORECAST.ETS.SEASONALITY

Returns the length of the repetitive pattern Excel detects for a specified time series.

Forecast.Ets.Seasonality Function Example

If you have a series of monthly earnings, the Forecast.Ets.Seasonality function can be used to detect the length of a seasonal pattern, as shown in the following spreadsheet:

Example of use of the Excel Forecast.Ets.Seasonality Function
FORECAST.ETS.STAT

Returns a statistical value relating to a time series forecasting.

Forecast.Ets.Stat Function Example

The spreadsheet below shows a series of monthly earnings between Aug-2016 & Apr-2017, and the Forecast.Ets function has been used in cell G11, to predict the earnings for May 2017.

The Forecast.Ets.Stat function is used in cell C13 of the spreadsheet, to return the Alpha parameter of the ETS algorithm:

Example of use of the Excel Forecast.Ets.Stat Function
FORECAST.LINEAR

Predicts a future point on a linear trend line fitted to a supplied set of x- and y- values.

Forecast.Linear Function Example

In the following spreadsheet, the Forecast.Linear function is used to predict the value of the trend line through the given x- and y- values in cells F2-G7, at the value x=7:

Example of use of the Excel Forecast.Linear Function
MAXIFS

Returns the largest value from a subset of values in a list that are specified according to one or more criteria.

Maxifs Function Example

 ABC
1StudentGroupScore
2Jane157%
3Lucy287%
4James275%
5Kevin141%
6Joseph177%
7Emma169%
8Alice289%
9Graham273%

In the above spreadsheet on the right, the maximum exam score recorded in Group 1, can be found by using the Maxifs function as follows:

=MAXIFS( C2:C9, B2:B9, 1 )

which gives the result 77%.

MINIFS

Returns the smallest value from a subset of values in a list that are specified according to one or more criteria.

Minifs Function Example

 ABC
1StudentGroupScore
2Jane157%
3Lucy287%
4James275%
5Kevin141%
6Joseph177%
7Emma169%
8Alice289%
9Graham273%

In the above spreadsheet on the right, the minimum exam score recorded in Ggroup 2, can be found by using the Minifs function as follows:

=MINIFS( C2:C9, B2:B9, 2 )

which gives the result 73%.