Returning Arrays

link - https://support.office.com/en-gb/article/excel-functions-that-return-ranges-or-arrays-7d1970e2-cbaa-4279-b59c-b9dd3900fc69


Functions that always return an Array

The following is a list of functions that always return an array.

FREQUENCYThe number of times a value occurs in a given array or cell range.
LINESTThe array of values for a straight line that best fits your data.
FREQUENCY
The number of times a value occurs in a given array or cell range.
LINEST
The array of values for a straight line that best fits your data.

Functions that can return an Array

The following is a list of functions that can return an array, depending on the arguments.

CHOOSEThe value in a row (or column) based on an index number.
COLUMNThe column number of the cell reference.
FORECASTThe future value along a linear trend by using existing values.
GROWTHThe predicted exponential growth using existing data.
LOGESTThe array of values for an exponential curve that best fits your data.
MINVERSEThe inverse matrix of an array.
MMULTThe matrix product of two arrays.
ROWThe row number of the cell reference.
TRANSPOSEThe array with its orientation changed.
TRENDThe y-values along a linear trend given a set of x-values.
CHOOSE
The value in a row (or column) based on an index number.
COLUMN
The column number of the cell reference.
FORECAST
The future value along a linear trend by using existing values.
GROWTH
The predicted exponential growth using existing data.
LOGEST
The array of values for an exponential curve that best fits your data.
MINVERSE
The inverse matrix of an array.
MMULT
The matrix product of two arrays.
ROW
The row number of the cell reference.
TRANSPOSE
The array with its orientation changed.
TREND
The y-values along a linear trend given a set of x-values.

Functions that cannot return an Array

The following is a list of functions that cannot return an array.

ABSThe absolute value, i.e. a number without its sign.
ABS
The absolute value, i.e. a number without its sign.

Using Database Functions

If you are using array formulas to help filter out data by using conditions, then it is far more efficient to use the database functions instead.


© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext