How to Do Vector Computations in Excel

By Peter Flom

A vector is a list of two or more numbers. A row vector has numbers lined up in a row, and a column vector has them lined up in a column. Two vectors can be added or subtracted if they are both the same type of vector and have the same dimension. Two vectors can be multiplied if they have the same dimension and one is a column vector and one is a row vector. If A is a row vector with 5 numbers and B is a column vector with 5 numbers, then A*B will be a 5*5 matrix, while B*A will be a single number (also called a scalar). Excel has functions to do all of these computations. They are known as array functions.

Step 1

Open Excel. Enter the vectors. For example, if A is 1, 2, 3, 4 and 5 and B is 2, 4, 6, 8 and 10, enter those numbers in columns A and B.

Step 2

Add the vectors. Highlight an area of the same size as A and then enter =(A1:Ap)+(B1:Bp), where p is the number of rows. Then simultaneously press "Ctrl," "Shift" and "Enter." In the example, highlight cells C1 to C5, type =(A1:A5)+(B1:B5) and then press "Ctrl," "Shift" and "Enter." The result should be 3, 6, 9, 12 and 15 in cells C1 to C5.

Step 3

Subtract the vectors. This is identical to adding numbers, except that you type - instead of + in the formula. In the example, highlight D1 to D5, type =(A1:A5)-(B1:B5) and press "Ctrl," "Shift" and "Enter." The result should be -1, -2, -3, -4 and -5 in cells D1 to D5.

Step 4

Multiply array A by array B. Remember that two matrices can be multiplied only if one is a row vector and one is a column vector. The act of changing a row to a column vector is called transposition. To multiply a row vector A by a column vector B, highlight an array that is pxp and enter =MMULT(TRANSPOSE(A1:Ap),(B1:Bp)) and press "Ctrl," "Shift" and "Enter." In the example, highlight cells E1 through J5, type =MMULT(TRANSPOSE(A1:A5),(B1:B5)) and press "Ctrl," "Shift" and "Enter." The result should fill all of the cells.