Page 87 - Excel for Scientists and Engineers: Numerical Methods

64 EXCEL: NUMERICAL METHODS

Examples:
In the following example the range B13:D15, containing the values {13,0,-
1;5,12,22;-5,0,1}, was assigned the name A.
The expression Mlndex(MAX(A),A) returns the array of values {2,3}.
The expression Mlndex(7,A) returns the array of values {#N/A,#N/A}.
The expression Mlndex(l5,A,I) returns the array of values {I ,I}.
Scaling Arrays. The function MSCALE(array, sca/e-facfor-/ogicaf) calculates
and applies scale factors for a N x M matrix and returns a N x M scaled matrix.
All values in a row are scaled by dividing by the largest element in that row. The
function also creates a column vector of N elements, containing the scale factors.
If the optional argument scale-factor-logical = False or omitted, the function
returns the scaled matrix; if sca/e-factor_/ogical= True, returns the scale factor
vector.
Examples:
In the following examples the range A5:C7, assigned the name B, contains
the values {3,20,1000;-0.1,3,100;5,10,-5).
The formula =MSCALE(B) returns the array {0.003,0.02,1;-
0.001,0.03,1~0.5,1,-0.5).
The formula =MSCALE(B,TRUE) returns the array {0.001;0.01;0.1}.

Combining Separate Ranges into a Single Array. An array in Excel
must be a contiguous range of cells. It sometimes happens that one would like to
combine noncontiguous ranges into a single array. The function Arr(rangel,
range2 ...) combines individual 1-D or 2-D arrays into a 2-D array. All individual
arrays must be vertical and must have the same number of rows. The VBA code
for the function is shown in Figure 3-7.
This custom function makes use of the ParamArray keyword, which allows
the function to accept an arbitrary number of ranges.
Some uses for this custom function include the following:
In the solution of a system of simultaneous equations by the Gaussian
Elimination method (see Chapter 9), an augmented matrix of N rows x N + 1
columns is created by combining the N x N matrix of coefficients with the N
rows x 1 column vector of constants. This can conveniently be done by using the
custom function.
The LINEST worksheet function for multiple linear regression (see Chapter
13) requires that the argument known-x's be a contiguous selection of cells. The
custom function can be used to convert a series of noncontiguous ranges into an
array that can be used as the argument known-x's in LINEST.