Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Matrix in Excel (Read 3509 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Matrix in Excel
06.04.10 at 09:35:57
Matrix in Excel
Microsoft's Excel spreadsheet program provides an excellent environment for matrix and vector analysis.  
Many Excel formulas require the specification of one or more ranges of cells as arguments. In many cases the easiest way to indicate such a range is to select it using keystrokes and/or a mouse as the formula is typed. We introduce an alternative approach, using named ranges in our formulas and statements. Since names remain with the formulas and statements, it is easy to change the physical range of cells to which a name applies whenever results are desired for a different range of inputs. They act as constants and when copying cells with formulas these named ranges stay indeed constant. The use of range names can also improve the readability of a set of formulas or statements.
The safest way to assign a name to a range of cells is to first select it, then choose Insert Name Define from the menu, followed by the desired name. Names that can be regarded as cell addresses should be avoided (like A2, R (column R), ...). I usually type an underscore first and then a name (e.g. _Rotation, _M, _A) so I can use all names I want.  You can also type the name in the field where the cell address is displayed. A handy shortcut.
Once you have named a range, you may use it in any formula that allows for a range as an argument.  
Unbeknownst to many users, Excel can do matrix operations very efficiently, either directly, or through the use of matrix functions. Microsoft prefers to use the term "Array" to "Matrix", so most references in their manuals and help system 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 can be a matrix, a vector, or a scalar, depending on the computations involved. Whatever the result may be, an area on the spreadsheet of precisely the correct size must be selected before the formula is typed in (otherwise you will either lose some of the answer or get added 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. It also designates the entire selected range as the desired location for the answer. To modify or delete the formula, select the entire region beforehand.
When matrix computations are performed in this way, the "result areas" will be 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 one wishes to evaluate the effects of changes in assumptions, initial conditions, etc.. This feature, coupled with the ability to see matrices, complete with identification of the rows and columns (i.e. in the form that we have termed tables), will often make the spreadsheet environment the preferred choice for computation, if not for communication.
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1