Excel functions with array arguments


This module introduces:


Here is a list of four functions that must be completed as CSE formulas. If entered via the function wizard you must not click the OK button (equivalent to the Enter key)


Excel functions

Enter as an Array Formula
Control + Shift + Enter		Description
MDETERM(array) Matrix Determinant: Returns the matrix determinant of an m x m array. Uses linear algebra
MINVERSE(array) Matrix Inverse: Returns the inverse matrix of an m x m array. Uses linear algebra
MMULT(array1, array2) Matrix Multiplication: Returns the matrix product of two arrays. The arrays must be conformable for multiplication (m x n, n x m returns m x m). Uses linear algebra
TRANSPOSE(array) Returns the transpose of the array. A horizontal range (or row vector) is returned as vertical range (or column vector). An m x n array is returned as an n x m array


In contrast, the SUMPRODUCT function and other function in the next list, take array arguments but can be completed with the Enter key, or the OK button on the function wizard.


Excel functions

Enter		Description
COLUMNS(array) Returns the number of columns in an array or reference
CORREL(array1, array2) Returns the correlation coefficient of array1, array2
COVAR(array1, array2) Returns the covariance of paired data points in array 1 and array2
COVARIANCE.P(array1, array2)
[Excel 2010] 		Returns the population covariance of paired data points in array 1 and array2
COVARIANCE.S(array1, array2)
[Excel 2010] 		Returns the sample covariance of paired data points in array 1 and array2
ROWS(array) Returns the number of rows in an array or reference
SUMPRODUCT(array1, [array2], [array3], …) Multiplies element by element components in the arrays, and returns the sum of those products
FTEST(array1, array2) Returns the result of an F-test
F.TEST(array1, array2)
[Excel 2010] 		Returns the result of an F-test
PEARSON(array1, array2) Returns the Pearson product-moment correlation coefficient between array1 and array2
TTEST(array1, array2, tails, type) Returns the probability associated with a Student's t-Test
T.TEST(array1, array2, tails, type)
[Excel 2010] 		Returns the probability associated with a Student's t-Test


The most commonly used of these functions in finance are: MMULT, TRANSPOSE, SUMPRODUCT, CORREL, and the COVARIANCE variants. The SUMPRODUCT function by itself (and other functions in the list) are not "array formulas"" as they do not require CSE for entry.