Page 86 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 3 MATRICES 63

The function returns a single numerical value, not an array, and thus you do not
have to use CONTROL+SHIFT+ENTER. The value of the determinant of B,
represented by IBI, is 12.

Some Additional Matrix Functions

Some additional functions useful for working with arrays or matrices are
provided on the CD that accompanies this book. The additional functions are as
follows:

Identity Matrix. The function MIDENT(size) returns an identity matrix of a
specified size. The size argument is optional. Use size when you want to use an
identity matrix in a formula. Omit size when you want to fill a range of cells on
a worksheet with an identity matrix; the size of the matrix is then determined by
the size of the selection. If the selection is not a square matrix, the function
returns the #REF! error value.
The maximum allowable size is 63 x 63 (larger gives #VALUE! error).
Examples:
The expression MIDENT(3) returns (1 ,O,O;O, 1 ,O;O,O, 1).
The formula =MIDENT() entered in the range Al:E5 returns
{1,0,0,0,0;0,1,0,0,0;0,0,1,0,0;0,0,0,1,0;0,0,0,0,1}.
The formula =MIDENT() entered in the range Al:E6 returns #REF! in the
cells (the selection has five rows and six columns).

Finding the Position of a Value in an Array. The function
Mlndex(/ookup-value, array, match-type) returns a horizontal 2-element array
containing the row and column numbers of a specified value in an array. The
argument lookup-value is the value you use to find the value you want in array-.
The argument array- is a contiguous range of cells containing possible lookup
values. The argument match-type is a number (-1, 0, or 1) that specifies the
value found in array-. If match-type is 0 or omitted, the function returns the
position of the value that is exactly equal to lookup-value, or #N/A. If
match-type is 1, the function returns the position of the largest value that is less
than or equal to lookup-value. If match-type is -1, the function returns the
position of the smallest value that is greater than or equal to lookup-value.
Unlike Excel's INDEX worksheet function, if match-type is -1 or 1, the values do
not have to be sorted in descending or ascending order, respectively.
The array must contain only numbers. If any cells contain text or error
values, Mlndex returns the #VALUE! error value. Empty cells are treated as zero.