Matrix operations
Unbeknownst to many users, Excel can do matrix operations very efficiently, eitherdirectly, or through the use of matrix functions. Microsoft prefers to use theterm "Array" to "Matrix", so most references in their manuals and helpsystem can be found under the former term.
Key to understanding the use of matrix operations in Excel is the concept of the Matrix(Array) formula. Such a formula uses matrix operations and returns a result that canbe a matrix, a vector, or a scalar, depending on the computations involved. Whatever theresult may be, an area on the spreadsheet of precisely the correct size must be selectedbefore the formula is typed in (otherwise you will either lose some of the answer or getadded and possibly confusing information).
After typing such a formula, you "enter" it with three keys pressed at once:CTRL, SHIFT and ENTER. This indicates that a matrix (array) result really is desired. Italso designates the entire selected range as the desired location for the answer. Tomodify or delete the formula, select the entire region beforehand.
When matrix computations are performed in this way, the "result areas" willbe updated immediately whenever any of the numbers in the "input areas" change(unless automatic recomputation has been turned off). This can be a great help when onewishes to evaluate the effects of changes in assumptions, initial conditions, etc.. Thisfeature, coupled with the ability to see matrices, complete with identification of therows and columns (i.e. in the form that we have termed tables), will often makethe spreadsheet environment the preferred choice for computation, if not forcommunication.
In Excel, some matrix operations are performed automatically, using standard operators(as in MATLAB). Others require the use of matrix functions. We treat each below.
Unbeknownst to many users, Excel can do matrix operations very efficiently, eitherdirectly, or through the use of matrix functions. Microsoft prefers to use theterm "Array" to "Matrix", so most references in their manuals and helpsystem can be found under the former term.
Key to understanding the use of matrix operations in Excel is the concept of the Matrix(Array) formula. Such a formula uses matrix operations and returns a result that canbe a matrix, a vector, or a scalar, depending on the computations involved. Whatever theresult may be, an area on the spreadsheet of precisely the correct size must be selectedbefore the formula is typed in (otherwise you will either lose some of the answer or getadded and possibly confusing information).
After typing such a formula, you "enter" it with three keys pressed at once:CTRL, SHIFT and ENTER. This indicates that a matrix (array) result really is desired. Italso designates the entire selected range as the desired location for the answer. Tomodify or delete the formula, select the entire region beforehand.
When matrix computations are performed in this way, the "result areas" willbe updated immediately whenever any of the numbers in the "input areas" change(unless automatic recomputation has been turned off). This can be a great help when onewishes to evaluate the effects of changes in assumptions, initial conditions, etc.. Thisfeature, coupled with the ability to see matrices, complete with identification of therows and columns (i.e. in the form that we have termed tables), will often makethe spreadsheet environment the preferred choice for computation, if not forcommunication.
In Excel, some matrix operations are performed automatically, using standard operators(as in MATLAB). Others require the use of matrix functions. We treat each below.
Not surprisingly, a matrix can be subtracted from one of the same size in a manneranalogous to that of addition. For example to find the holdings of account 2, you coulduse the formula:
= Tot_Holdings - Holdings_1
To add a constant to every element of a matrix, simply include it in a formula, as in:
= Tot_Holdings + 100You can also subtract a constant from every element or multiply or divide every elementby a constant. For example:
= Prices * 1.10
To multiply two matrices, use the MMULT function. Thus, if prices and holdingsare compatible for multiplication, you could compute the value of a portfolio with theformula:
= MMULT(prices,holdings)
Comments
No comments yet. Be first!