Updates
New Functions - Microsoft 365
| ARRAYTOTEXT | Returns the contents of an array or cell range converted into text. |
| BYCOL | Returns the results from applying a LAMBDA function to each column in an array. |
| BYROW | Returns the results from applying a LAMBDA function to each row in an array. |
| CHOOSECOLS | Returns the array with just a specific number of columns. |
| CHOOSEROWS | Returns the array with just a specific number of rows. |
| DROP | Returns the array without certain rows or columns. |
| EXPAND | Returns the array expanded or padded to specific dimensions. |
| FIELDVALUE | Returns the field data from a Stocks or Geography linked data type. |
| GROUPBY | (2023) Returns the grouping of your data along one axis and aggregates the associated values. |
| HSTACK | Returns the array after combining two arrays horizontally in sequence. |
| IMAGE | Returns the image from a web URL. |
| ISOMITTED | Returns the value True or False depending whether the value in a LAMBDA is missing. |
| LAMBDA | Returns the results of a formula added as a named range. |
| MAKEARRAY | Returns the array calculated by applying a LAMBDA function. |
| MAP | Returns the array formed by applying a map using a LAMBDA function. |
| PIVOTBY | (2023) Returns the grouping of your data along two axis and aggregates the associated values. |
| PY | (2023) Returns the value or object after running code in a Python Editor. |
| REDUCE | Returns the total value after reducing an array by applying a LAMBDA function. |
| SCAN | Returns the array after applying a LAMBDA function to each value and returns an array. |
| STOCKHISTORY | Returns the historical data about a financial instrument. |
| TAKE | Returns the intersection of specific rows and columns in an array. |
| TEXTAFTER | Returns the characters from the end of a text string after a delimiter. |
| TEXTBEFORE | Returns the characters from the start of a text string before a delimiter. |
| TEXTSPLIT | Returns the text string split into multiple columns using delimiters. |
| TOCOL | Returns the array transformed into a single column. |
| TOROW | Returns the array transformed into a single row. |
| TRIMRANGE | (2024) Preview. Returns the array after excluding all empty rows and/or columns from the outer edges of a range. |
| VALUETOTEXT | Returns the text from any specified value. |
| VSTACK | Returns the array after combining two arrays vertically in sequence. |
| WRAPCOLS | Returns the array transformed into multiple columns. |
| WRAPROWS | Returns array transformed into multiple rows. |
| ARRAYTOTEXT Returns the contents of an array or cell range converted into text. |
| BYCOL Returns the results from applying a LAMBDA function to each column in an array. |
| BYROW Returns the results from applying a LAMBDA function to each row in an array. |
| CHOOSECOLS Returns the array with just a specific number of columns. |
| CHOOSEROWS Returns the array with just a specific number of rows. |
| DROP Returns the array without certain rows or columns. |
| EXPAND Returns the array expanded or padded to specific dimensions. |
| FIELDVALUE Returns the field data from a Stocks or Geography linked data type. |
| GROUPBY (2023) Returns the grouping of your data along one axis and aggregates the associated values. |
| HSTACK Returns the array after combining two arrays horizontally in sequence. |
| IMAGE Returns the image from a web URL. |
| ISOMITTED Returns the value True or False depending whether the value in a LAMBDA is missing. |
| LAMBDA Returns the results of a formula added as a named range. |
| MAKEARRAY Returns the array calculated by applying a LAMBDA function. |
| MAP Returns the array formed by applying a map using a LAMBDA function. |
| PIVOTBY (2023) Returns the grouping of your data along two axis and aggregates the associated values. |
| PY (2023) Returns the value or object after running code in a Python Editor. |
| REDUCE Returns the total value after reducing an array by applying a LAMBDA function. |
| SCAN Returns the array after applying a LAMBDA function to each value and returns an array. |
| STOCKHISTORY Returns the historical data about a financial instrument. |
| TAKE Returns the intersection of specific rows and columns in an array. |
| TEXTAFTER Returns the characters from the end of a text string after a delimiter. |
| TEXTBEFORE Returns the characters from the start of a text string before a delimiter. |
| TEXTSPLIT Returns the text string split into multiple columns using delimiters. |
| TOCOL Returns the array transformed into a single column. |
| TOROW Returns the array transformed into a single row. |
| TRIMRANGE (2024) Preview. Returns the array after excluding all empty rows and/or columns from the outer edges of a range. |
| VALUETOTEXT Returns the text from any specified value. |
| VSTACK Returns the array after combining two arrays vertically in sequence. |
| WRAPCOLS Returns the array transformed into multiple columns. |
| WRAPROWS Returns array transformed into multiple rows. |
New Functions - Office 2021
| FILTER | Returns the array of data in a range that satisfies multiple conditions. |
| LET | Returns the result of a formula that can use variables. |
| RANDARRAY | Returns the array of random numbers between 0 and 1. |
| SEQUENCE | Returns the array of sequential numbers. |
| SORT | Returns the array of data in a range that has been sorted. |
| SORTBY | Returns the array of data in a range that has been sorted based on the values in a corresponding range. |
| UNIQUE | Returns the array of unique values in a list, table or cell range. |
| XLOOKUP | Returns the value in the same row after finding a matching value in any column. |
| XMATCH | Returns the position of a value in a list, table or cell range. |
| FILTER Returns the array of data in a range that satisfies multiple conditions. |
| LET Returns the result of a formula that can use variables. |
| RANDARRAY Returns the array of random numbers between 0 and 1. |
| SEQUENCE Returns the array of sequential numbers. |
| SORT Returns the array of data in a range that has been sorted. |
| SORTBY Returns the array of data in a range that has been sorted based on the values in a corresponding range. |
| UNIQUE Returns the array of unique values in a list, table or cell range. |
| XLOOKUP Returns the value in the same row after finding a matching value in any column. |
| XMATCH Returns the position of a value in a list, table or cell range. |
New Functions - Office 2019
| CONCAT | Returns the text string that is a concatenation of several strings. Replaces CONCATENATE |
| IFS | Returns the value based on multiple conditions you specify. |
| MAXIFS | Returns the largest value in a list or array of numbers that satisfies multiple conditions. |
| MINIFS | Returns the smallest value in a list or array of numbers that satisfies multiple conditions. |
| SWITCH | Returns the value based on a list of matching items. |
| TEXTJOIN | Returns the text string that is a concatenation of several strings. |
| CONCAT Returns the text string that is a concatenation of several strings. Replaces CONCATENATE |
| IFS Returns the value based on multiple conditions you specify. |
| MAXIFS Returns the largest value in a list or array of numbers that satisfies multiple conditions. |
| MINIFS Returns the smallest value in a list or array of numbers that satisfies multiple conditions. |
| SWITCH Returns the value based on a list of matching items. |
| TEXTJOIN Returns the text string that is a concatenation of several strings. |
New Functions - Office 2016
| FORECAST.ETS | Returns a future value based on historical values. |
| FORECAST.ETS.CONFINT | Returns a confidence interval for a future value. |
| FORECAST.ETS.SEASONALITY | Returns the length of the repetitive seasonality pattern. |
| FORECAST.ETS.STAT | Returns a statistical value for a future value. |
| FORECAST.LINEAR | Returns the future y-value based on existing values. |
| FORECAST.ETS Returns a future value based on historical values. |
| FORECAST.ETS.CONFINT Returns a confidence interval for a future value. |
| FORECAST.ETS.SEASONALITY Returns the length of the repetitive seasonality pattern. |
| FORECAST.ETS.STAT Returns a statistical value for a future value. |
| FORECAST.LINEAR Returns the future y-value based on existing values. |
AutoComplete - Function autocomplete will now return any functions that contain the characters you type in.
CEILING and FLOOR have been moved to Compatibility category.
The Database category of functions (still) does not have its own drop-down on the Formulas tab.
Office 2013
| ISOWEEKNUM | Returns the number of the ISO week of the year for a given date. |
| ISOWEEKNUM Returns the number of the ISO week of the year for a given date. |
Office 2010
61 New Worksheet Functions
AGGREGATE returns an aggregate in a list or database. Replaces SUBTOTAL
BETA.DIST has an additional parameter to specify the type of distribution (left-tailed cumulative or probability density).
CRITBINOM returns the inverse of the binomial distribution but BINOM.INV is a much more intuitive name.
ERF can now accept negative values.
ERFC can now accept negative values.
MOD can take larger input values.
OFFSET arguments have different rounding.
Function Renaming - A number of existing functions have been renamed for consistency.
Improved Accuracy - Several of the Statistic Functions have had their accuracy improved.
Asynchronous - User Defined Functions can run asynchronously without using multiple Excel calculation threads.
VBA - Application.MacroOptions - ArgumentDescriptions - An additional argument has been added to the Application.MacroOptions method. It is not possible to provide argument descriptions for your User Defined Functions.
Office 2007
12 New Worksheet Functions
All the Analysis-ToolPak functions are now standard worksheet functions and are available without having to install the Analysis-ToolPak add-in.
Office 2003
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext