# Excel functions with array arguments

This module introduces:

- a selection of Excel functions that take explicit array arguments (arguments named array)
- Note: mathematical operators and many other functions also form the basis for array (CSE) formulas

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.

**Development platform:**Excel 2016 (64 bit) Office 365 ProPlus on Windows 10**Revised:**Thursday 28th of June 2018 - 07:45 AM, [Australian Eastern Time (AET)]