Microsoft KB Archive/43967

Excel Function Macro to Create Unit Vector PSS ID Number: Q43967 Article last modified on 02-26-1993 PSS database name: W_eXceL

2.x 3.00

WINDOWS

Summary:

Below is a function macro that computes a unit vector (a vector of length or magnitude 1) for any vector (an nx1 or 1xn dimensional array) on a worksheet.

More Information:

Assume the following macro is on macro sheet VECTOR.XLM. The macro named “unitvect” is as follows:

A1: unitvect A2: =RESULT(64) A3: =ARGUMENT(“myarray”,64) A4: {=SET.NAME(“myarray”,myarray/SQRT(SUM(myarray^2)))} A5: =RETURN(myarray)

The following illustrates an example of unitvect’s use. On worksheet SAMPLE.XLS enter the following vector in cells A1 through E1:

A      B       C       D       E 1 2 4 6 8 10

Highlight cells A2 through E2 and type in the following, entering the formula with CTRL+SHIFT+ENTER to enter it as array:

=VECTOR.XLM!UNITVECT(A1:E1)

The function macro returns the following unit vector to cells A2:E2:

A      B          C        D         E 1 2 4 6 8 10 2 0.13484 0.26968 0.40452 0.53936 0.6742

Note: Line A4 (of the macro) above must not be typed with the “{}”s surrounding it. This entry is entered as an array formula (by typing in everything in A4 except the {}’s and entering the formula with a CTRL+SHIFT+ENTER).

Copyright Microsoft Corporation 1993.