Buzz
As we all know, Microsoft Excel tools in Office 2016, as well as in Office 2007 or Office 2003, have mathematical functions to serve useful work and study purposes. However, in Excel in Office 2016, Microsoft has updated additional new functions that are very useful, which are not available in Office 2007 or Office 2003. The following compilation of Excel functions exclusive to Microsoft Office 2016 will help you understand the significance and usage of these new functions.
Compilation of Excel functions exclusive to Office 2016
Text Excel Functions
- CONCAT Function in Excel 2016
Merge two or more text strings.
Example of the Concat function:
In the spreadsheet below, you can link text strings in cells A1, B1, and C1 together using the Excel Concat function as shown below:
=CONCAT( A1:C1 )
And provide the text string result: C:\Users\Jeff.
- TEXTJOIN Function in Excel 2016
Merge 2 or more text strings, separated by spaces.
- Example of the Textjoin function:
In the spreadsheet below, you can link text strings in cells A1, B1, and C1 together, separated by spaces, using the textjoin function as shown below:
=TEXTJOIN( '\', TRUE, A1:C1 )
The function provides the result text string C:\Users\Jeff.
Logic Functions in Excel 2016
- IFS Function in Excel 2016:
Checks whether one or multiple conditions are met and returns the value corresponding to the first TRUE condition.
Example of the IFS function:
In the spreadsheet below, the IFS function can be used to divide the value in cell A1 by a value other than the first value in cells B1-C1:
= IFS (B1 <> 0, A1 / B1, C1 <> 0, A1 / C1)
Providing a result of 10.
(For example: value in cell A1 divided by value in cell C1).
- SWITCH Function in Excel 2016:
Compares a value, called an expression, against a list of values and returns the result corresponding to the first matching value.
Example of the Switch function:
In the spreadsheet below, the switch function is used to return the result of a season related to the value in cell A1.
=SWITCH( A1, 1, 'Spring', 2, 'Summer', 3, 'Autumn', 4, 'Winter' )
Providing the result as Summer.
Statistical Functions in Excel 2016
- FORECAST.ETS Function in Excel 2016:
Utilizes an exponential smoothing algorithm to predict future values at a specified time interval, based on a series of existing values.
Example of the Forecast.Ets Function :
If computing monthly income predictions, use the Forecast.Ets function to forecast income for the next month, as illustrated in the spreadsheet below:
- FORECAST.ETS.CONFINT Function in Excel 2016:
Returns the confidence interval for the forecasted value at a specified target date.
Example of the Forecast.Ets.Confint Function:
The spreadsheet below displays a series of income ranges from August 2016 to April 2017, with the Forecast.Ets function used in cell G11 to predict future income in May 2017.
The Forecast.Ets.Confint function is used in cell C13 to calculate the reliable time interval for the forecasted value:
Compilation of Excel functions exclusive to Office 2016
- FORECAST.ETS.SEASONALITY 2016 Function:
Returns the length of the repeating pattern that Excel detects for a specified time series
Example of using the Forecast.Ets.Seasonality function:
If there is a series of monthly income, use the Forecast.Ets.Seasonality function to detect the length of a pattern, similar to the table below:
- FORECAST.ETS.STAT 2016 Function:
Returns a statistical value as the result of the time series forecasting process.
Example of using the Forecast.Ets.Stat function:
The spreadsheet below displays a series of monthly incomes from August 2016 to April 2017, the Forecast.Ets function is used in cell G11 to predict the income for May 2017.
The Forecast.Ets.Stat function is used in cell C13 to return the Alpha parameter of the ETS algorithm:
- FORECAST.LINEAR function in Excel 2016:
Predicting a future point on the trendline set to a set of x and y values.
Example of using the Forecast.Linear function:
In the spreadsheet below, the Forecast.Linear function is used to predict the value of the trendline through the available x and y values in cells F2-G7 with x value = 7:
- MAXIFS function in Excel 2016:
Returns the maximum value between cells specified by a set of given conditions or criteria.
Example of the Maxifs function:
In the spreadsheet below, use the Maxifs function to calculate the maximum test score provided in Group 1:
=MAXIFS( C2:C9, B2:B9, 1 )
The provided result is 77%.
- MINIFS function in Excel 2016:
Returns the minimum value between cells specified by a set of given conditions or criteria.
Example of the Minifs function:
In the spreadsheet below, use the Minifs function to calculate the minimum test score in Group 2:
=MINIFS( C2:C9, B2:B9, 2 )
The provided result is 73%.
These are summaries of Excel functions exclusive to Office 2016 that are not available in Office 2007 and Office 2003, newly updated to help users easily filter, format, and perform calculations in Excel.
Returning to Excel's functions present in all versions, SumIF is one of the most commonly used functions, supporting summing with conditions in the spreadsheet as users require. Furthermore, when combining SumIF with other functions, your tasks or work can be processed more efficiently.
