MAGNITUDE

=MAGNITUDE(vector)

Required: COLSUM

Download the Template

In the template file, navigate to the Matrices worksheet to see the MAGNITUDE function in action.

Description

The MAGNITUDE of a vector is the square root of the sum of the squares of the elements. There are a number of different ways to calculate this in Excel when a is a vector:

=MAGNITUDE(a)
=SQRT(SUM(a*a))
=SQRT(SUM(a^2))
=SQRT(SUMPRODUCT(a,a))
=SQRT(DOT(a,a))
=SQRT(SUMSQ(a))

If the matrix A contains multiple columns, then MAGNITUDE(A) returns a row vector containing the magnitudes of the individual columns. This is similar in behavior to the vecnorm function in Matlab.

=SQRT(COLSUM(A^2))

Lambda Formula

This code for using MAGNITUDE in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

/**
* Returns the magnitude (2-norm) of a vector or the magnitude of each column of a matrix
*/
MAGNITUDE = LAMBDA(vector,
LET(doc,"https://www.vertex42.com/lambda/magnitude.html",
    vec,IF(AND(ROWS(vector)=1,COLUMNS(vector)>1),TRANSPOSE(vector),vector),
    IF(COLUMNS(vec)>1,
        SQRT(COLSUM(vec*vec)),
        SQRT(SUM(vec*vec))
    )
));

Name: MAGNITUDE
Description: Calculates the magnitude of a vector as SQRT(SUM(x^2))
Requirements: COLSUM
Arguments: vector
Function:

LET(doc,"https://www.vertex42.com/lambda/magnitude.html",
    vec,IF(AND(ROWS(vector)=1,COLUMNS(vector)>1),TRANSPOSE(vector),vector),
    IF(COLUMNS(vec)>1,
        ARRAYFORMULA(SQRT(COLSUM(vec*vec))),
        ARRAYFORMULA(SQRT(SUM(vec*vec)))
    )
)

MAGNITUDE Examples

Test: Copy and Paste this LET function into a cell
=LET(
    a, {4;-4;2},
    b, {0;-3;4},
    c, {1;1;1},
    matrix, HSTACK(a,b,c),
    MAGNITUDE(matrix)
)

Result: {6, 5, 1.732}

=LET(
    vector, {0.4934;-0.8654;-0.0873},
    MAGNITUDE(vector)
)

Result: 0.999991

=LET(
    vector, {-3; 0; 4},
    vector/MAGNITUDE(vector)
)

Result: {-0.6; 0; 0.8}