Introduction to Excel Array Functions.docVIP

Introduction to Excel Array Functions This document is intended to introduce the use of Excel to perform basic matrix algebra operations. Sumproduct Definition For matrices A and B, and Sumproduct(A, B) Notes: r = i and c = j. In other words, the two matrices must have the same number of rows as each other and the same number of columns as each other. They do not need to be square matrices (where r = c and i = j). Algebra geeks sometimes call this operation the “dot product” (to distinguish it from matrix multiplication — described later in this document), and symbolize it as . Example and Sumproduct(A, B) Excel Method Transpose Definition For matrix A, Notes: If A is an r x c matrix, then and must be a c x r matrix. A does not need to be a square matrix. Example Excel Method There is an Excel function for this purpose, called TRANSPOSE. This function is one of a special class of functions called array functions. In contrast with most other Excel functions, array functions have two important differences: They are entered into ranges of cells, not single cells You enter them by pressing Shift+Ctrl+Enter, not just Enter Using the spreadsheet above as an example, we start by selecting the entire range A4:C6. Then type into the formula bar =TRANSPOSE(A1:C2) Press Shift+Ctrl+Enter, and curly brackets will appear round the formula (you can’t type them in). Multiplication Definition For matrices A and B, and Notes: It is conventional to describe the shape of a matrix by listing the number of rows first, and the number of columns second. Matrix A above is an r x c matrix, and matrix B is an i x j matrix. In this operation, it is necessary for c = i. However it is not necessary for r = j. In other words, B must have the same number of rows as A has columns, but it is not necessary for B to have the same number of columns as A has rows. The product AB will always be an r x j matrix. Example and Excel Method Remember: Select the entire ra