FactorPad
Faster Learning Tutorials

The operations of array math and matrix multiplication

Linear algebra is used in nearly every industry for the statistical analysis of large data sets. Here we start with the basics.
  1. Conventions - Outline reasons for and the conventions associated with Linear Algebra.
  2. Array types - Define scalars, vectors and matrices.
  3. The Operations - Review five common array math operations.
  4. Tips & Tricks - Navigate array formulas and array functions in Excel with named data ranges.
  5. Next: Portfolio algebra - Apply array math to portfolio return and risk calculations.
by Paul Alan Davis, CFA, May 26, 2018
Updated: July 22, 2018
Because arrays are used as primary data structures in computer programming languages, those who understand arrays have higher career upside. Keep reading to learn how.

Outline Back Next

~/ home  / finance  / quant 101  / array math


Learn how to manipulate huge data sets using array and matrix math

Beginner

Watch the Video

The operations of array math and matrix multiplication (18:51)

Videos are available at one organized Quant 101 Playlist on YouTube (opens in a new browser window).

Video Script

Welcome. Today's goal is to learn the primary operations of Linear Algebra so we can manipulate huge data sets in all fields involving data analysis, or to simply pass a test.

I'm Paul, and if you are confused by Linear Algebra then you are not alone. Just finding a quality resource for the basics is a challenge.

So here we will solve the problem and use scalars, vectors and matrices to cover the basic mathematical operations. Our playground here is Excel and we will see tips so you can confidently manipulate arrays in a spreadsheet.

If you'd like to read the video transcript and see the tables the first link in the video Description goes straight to a web page if you want to slow it down.

Here's our plan for the day.

Outline

First, we cover the conventions with Linear Algebra beginning with why we even use it in the first place.

Second, we cover the types of arrays.

Third, we walk through five exercises covering the must-know operations.

Fourth, we cover tips and tricks for simplifying array math in Excel.

And in our next episode we will return to our financial data set and get practice with these operations.

Step 1 - Conventions with Linear Algebra

For Step 1, let's start with some conventions applied to Linear Algebra.

a. Why use Linear Algebra?

Before we start with that, what is the purpose of using Linear Algebra anyway? And how is it practical in the real world?

Linear algebra is used widely in computer systems to make calculations on large data sets. This allows you to write simpler formulas with fewer intermediate steps which first makes them faster and second saves computer disk space.

The third benefit is improved accuracy because we keep formulas intact and operate on rows and columns of data instead of each individual calculation, like individual cells in a spreadsheet.

Here in Excel we will be able to see this. Many people when creating spreadsheets write a unique formula in each cell. When using Linear Algebra we can treat columns and rows of data as units.

Also, on the point about speed, we will see how to make multiple calculations in one cell instead of taking intermediate steps.

b. Arrays

Let's start with the term 'array' which refers to an arrangement of data that can take many shapes.

Keep in mind, depending on context the term 'array' may be used differently. Two areas that come to mind are in computer programming and in the Linear Algebra branch of Mathematics.

Here we will use 'array' as a catch-all for all types of data structures, scalars, vectors and matrices, which we will define in a moment. So all of these structures are arrays.

c. Row-by-column convention

These arrays are often simply referred to by their dimensions because as we will see shortly the dimensions play an important part in the operations.

What is consistent is the row-by-column convention when specifying the dimensions of an array. So this is consistent across both computer programming and in Mathematics.

While in spreadsheets like Excel, our tool here, people often refer to structures of data in cells or ranges of cells, in computer programming and Linear Algebra, the dimensions are always named consistently using this row-by-column convention.

A structure is always given with the row first, then column. Yes, row-by-column.

I memorized it by thinking that the order isn't alphabetical, meaning r comes before c. So row first, colunn second.

Step 2 - Types of Arrays

For Step 2 let's define the names of arrays and describe their dimensions.

a. Scalar

First we have a scalar. A scalar is used to scale, which you can use to multiply a whole row or column of data.

  • Scalar (1x1)
    | 1 |

It always has dimensions of 1-by-1, one row and one column.

b. Vector

Next, a vector is said to be one-dimensional and it can be a row vector or column vector.

A row vector is a one-dimensional row of data, the example here has 1-by-3 dimensions, or 1 row by 3 columns.

  • Row vector (1x3)
    | 1  2  3 |

Next, we have an example of a column vector with 3 rows and 1 column.

  • Column vector (3x1)
    | 4 |
    | 5 |
    | 6 |
c. Matrix

A matrix is considered a multi-dimensional array, so it will have at least two rows and columns. Here we have an example of a 2-by-3 matrix.

  • Matrix (2x3)
    | 1   3   5 |
    | 2   4   6 |

Also a 3-by-2 matrix.

  • Matrix (3x2)
    | 1   2 |
    | 3   4 |
    | 5   6 |

There you have it, the three basic structures.

Step 3 - Array Math Operations

Okay, let's move on to Step 3. As mentioned our playground here is Excel and these examples we use can mostly be done in your head. That said, many people learn Linear Algebra on a pad and paper so if it helps to write it down please do so.

a. Excel Ctrl+Shift+Enter formula (CSE)

When we ask Excel to perform an array calculation we must submit it using the Ctrl+Shift+Enter keystroke combination. Oftentimes many calculations are performed made at once, so we need to tell Excel to employ array math using this keystroke combination that is at times abbreviated as a CSE formula.

Ok, now we're ready to walk through the five operations with exercises using the required rules from Linear Algebra.

b. Transpose a matrix

At times we need to reshape our data so it follows the rules. When we reshape it we call it transposing. You can think of it as turning the data on its side.

For example, you can change a matrix named A, with the format of 2-by-3, two rows and three columns, to what is called the Transpose of A with the dimensions of 3-by-2.

To do this manually we would put the first row down the first column, so it would read 1, 3 and 5 in the first column and 2, 4 and 6 in the second.

  E F G H I J
5 A: 2x3 matrix   Transpose A: 3x2 matrix
6 1 3 5   1 2
7 2 4 6 = 3 4
8   5 6

Here to do the same thing in Excel, we highlight the whole output range and use =TRANSPOSE(E6:G7) followed by the Ctrl+Shift+Enter keystroke combination and we get the same answer.

Transposing simply takes the rows and makes a columns out of them.

An example using real data

Visualizing this with real data may help. In our Quant 101 Playlist we use data located on a tab called Returns structured with dates down the rows with a corresponding return for the individual stocks stored across columns.

Let's use a 3-by-3 matrix from the top three rows and columns from this table and transpose them.

  B C D
6 Date MSFT EBAY
7 4/30/03 0.056175 0.080987
8 5/30/03 -0.037544 0.094070

We select the whole output block where we want the answer and type the formula =TRANSPOSE(Returns!B6:D8) followed by the CSE keystroke combination and now we have stocks down the rows and dates across the columns.

  M N O
5 Transpose Returns: 3x3 matrix
6 Date 4/30/03 5/30/03
7 MSFT 0.056175 -0.03754
8 EBAY 0.089087 0.09407

One last note, formally in a text book, you may see an array name which looks like it is raised to the power of T, meaning it has been transposed. So we may use shorthand A^T for matrix A transposed.

The main purpose of transposing is that it helps to align data so the mathematical operations work. On that, let's jump to the operations.

c. Addition and subtraction

For addition and subtraction the rules of Linear Algebra require that dimensions of the two arrays must be the same. The resulting array will have the same dimensions.

Array addition

Let's walk through an example, let's take a 3-by-1 vector B and add it to a 3-by-1 vector C.

Here to do it manually we simply add the first row of vector B to the first row of vector C and place it in the answer vector D. So 1 plus 4 is 5, 2 plus 5 is 7 and 3 plus 6 is 9.

  E F G H I
11 B: 3x1 vector   C: 3x1 vector   D: 3x1 vector
12 1   4   5
13 2 + 5 = 7
14 3   6   9

Let's do it in Excel now with an array formula. First, we select the whole answer block and enter =E12:E14+G12:G14 followed by CSE and our answer matches. You could put parentheses around the ranges to visualize them better, but that isn't required.

Array subtraction

The rules for subtraction are the same and to verify this in Excel let's subtract C from B with =E12:E14-G12:G14 in a 3-by-1 vector E.

  E F G M N
11 B: 3x1 vector   C: 3x1 vector   E: 3x1 vector
12 1   4   -3
13 2 - 5 = -3
14 3   6   -3

That's it for addition and subtraction, pretty straightforward. As mentioned, under the strict rules of Linear Algebra the arrays must have the same dimensions and the resulting array will have the same dimensions.

d. Matrix multiplication and division

Just as with addition and subtraction, multiplication and division share a set of rules, but the rules are different.

Array multiplication

Multiplication is the operation mentioned that helps you to perform many calculations at once, often to produce a 1-by-1 array result as we have here in cell K19.

In our first exercise let's multiply a 1-by-3 vector F times a 3-by-1 vector G and place the result in a single-cell output range.

  E F G H I J K
17 F: 1x3 vector   G: 3x1 vector   H: 1x1 array
18 1 3 5 * 4  
19   5 = 49
20   6  

Here we have three requirements. First, you need to line up the arrays properly. For example, here, a 1-by-3 vector multiplied by a 3-by-1 vector is aligned properly. This means that the inner dimensions must be the same, so 3 matches 3.

You couldn't multiply a 3-by-1 vector by a 3-by-1 vector, like vectors B and C for example because their inner dimensions don't match.

Second, when the arrays are aligned properly, the outer numbers give the dimensions of the resulting array, here 1-by-1.

And third, multiplying arrays takes the sum of the products. To visualize this think about the row, so 1, 3 and 5, times the whole column 4, 5 and 6. Take each of the multiplications and then sum them up.

To do this manually you would take 1 times 4, which is 4, then 3 times 5 which is 15 and finally 5 times 6 which is 30 and add these products together. So cell K19 is the sum of 4 plus 15 plus 30, or 49.

Matrix multiplication in Excel

Now to do the same thing in Excel we use a specialized =MMULT() function.

So we should be able to get the same answer with =MMULT(E18:G18,I18:I20) multiplying the first array F times the second G, with a comma in between the two arrays.

Matrix multiplication of a scalar and vector

Let's get more practice lining up the dimensions, this time multiplying a 1-by-1 scalar times a 1-by-3 vector. The inner dimensions match at 1 and the resulting answer is a 1-by-3 vector, named K.

  E F G H I J K L M
22 I: 1x1 scalar   J: 1x3 vector   K: 1x3 vector
23 4 * 1 3 5 = 4 12 20

Here there is no summation involved but we still need to highlight the whole output array, enter =MMULT(E23,G23:I23) followed by the CSE keystroke combination and we get a vector with 4, 12 and 20.

Her we can see how the scalar of 4 scaled each number in the J vector to produce the answers in vector K.

Matrix division

The rules for matrix division are the same as for multiplication however there is no function for matrix division so we will return to division after finding the inverse in Exercise 5.

e. The identity matrix

Next let's tackle what is called the identity matrix.

Of course you remember from Algebra that we frequently multiply both sides of an equation by one. The identity matrix basically is a square matrix that equals one.

It has ones down the diagonal and zeros in the other cells and must be structured like this to produce the same matrix after multiplication.

  E F G H I J K L
26 L: 2x2 matrix   M: 2x2 matrix   N: 2x2 matrix
27 1 2   1 0   1 3
28 2 4 * 0 1 = 2 4

To produce the resulting 2-by-2 matrix N let's highlight the output block and use =MMULT(E27:F28,H27:I28).

Notice how when you multiple matrix L with the identity matrix the result N looks just like L. That's what we wanted, we just multiplied a matrix by 1.

It might be good to pause here and walk through the math manually to solidify your understanding of matrix multiplication and the four different summation exercises going on in this one calculation.

Maybe take out a piece of paper, multiply the matrices together and see if you get the same answer. Let me describe the first cell, it is 1 times 1 equals 1, plus, 3 times 0 equals 0, and the total is 1, in the top-left.

f. Invert a matrix

As mentioned earlier with division, at times we need to invert an array.

If you recall from basic Algebra, the inverse is the same as 1-over a number, right? So the inverse of 2 is 1 over 2, or one-half.

In Linear Algebra, is a little trickier and we don't have time for it here. It involves taking 1 over the determinant times the adjugate. Look up those terms if you want to walk through the formula.

Here instead we will ask Excel to calculate it for us with the specialized =MINVERSE() function.

In Exercise 5 we will calculate the inverse of matrix O and name it P and when we multiply the two togeter we should get 1, or the identity matrix in Q, right?

  E F G H I J K L
31 O: 2x2 matrix   P: 2x2 matrix   Q: 2x2 matrix
32 1 3   -2.0 1.5   1 0
33 2 4 * 1.0 -0.5 = 0 1

Let's start with matrix P by highlighting the output range and then typing =MINVERSE(E32:F33) followed by the CSE keystrokes and this produces the Inverse.

To test it let's create the Q matrix with =MMULT(E32:F33,H32:I33) and CSE to get the identity matrix, or 1.

This is akin to our basic Algebra example of multiplying 2 times one-half which gives you 1.

Now, just like for the basic matrix multiplication example earlier, if you multiplied 1 times -2, you'd get -2, right, and add that to 3 times 1, or 3, and you get 1. That's the top left cell and I suggest finishing the calculation with a pad and paper on your own time.

This will solidify the concept of matrix multiplication showing you how it aggregates many calculations together saving us from having to create intermediate ranges to hold our data. This is one of the main reasons we noted for using arrays in the first place.

Don't forget to use =MMULT() here instead of the multiplication symbol. With addition and subtraction we don't need a special function but for multiplication and division we do.

Speaking of remembering, now that we understand the inverse, let's head back and finish off the division from earlier in Excercise 3b.

Matrix Division

What we will do here is take the inverse of the scalar in I and multiply that by the vector K and that should produce the exact same vector as we have in J, right?

  M N O
17 R: 1x3 vector
18 1 3 5

Let's select the output block of M18:O18 and then input =MMULT(MINVERSE(E23),K23:M23) followed by that CSE keystroke combination and voila, vector R matches matches vector J from earlier.

This is a little tricky but just think of the inverse operation as the first argument of the matrix multiplication. So the inverse needs to be wrapped inside the =MMULT() function and separated by a comma.

Step 4 - Helpful Tips for Excel

Okay we went over a lot there and Excel helped us out tremendously. Now for Step 4 we need to back up a minute and cover tips and tricks when working with arrays in Excel.

a. Named ranges

The first trick is using named ranges. We went over this in the last tutorial and basically they allow you to select ranges once and give them a name in the Name Box. That way you can refer to them by name instead of using the cell ranges. This helps to minimize errors and saves time when you use the same ranges frequently.

Naming ranges not only helps when working with arrays, but will also generally advance of your spreadsheet skills.

Head back to the Linear Algebra Introduction tutorial for the details.

b. Array formulas versus array functions

There we also touched on how array formulas we build on our own, like with addition and subtraction here differ from built-in array functions like =MINVERSE(), =MMULT() and =TRANSPOSE().

Technically, the rules of Linear Algebra Algebra for addition and subtraction require that the starting and ending arrays be the same size.

This rule can be broken using array formulas we build in Excel as we saw in that tutorial. As an example on your end try to subtract the array I from G and see if you get a 3-by-1 column vector containing 0, 1 and 2.

c. Curly braces and data integrity

Also, I wanted to return to the point made earlier about how Excel identifies array calculations for you so you can keep your data intact and treat your arrays as units.

For this, Excel offer three clues. First, Excel puts curly braces around all array formulas.

Second, if you try to delete or edit part of an array, Excel sends a warning.

Third, every cell in the whole array is given the same formula.

All of these help to keep the integrity of your data and arrays intact so you know there are no cells in the middle that have been edited.

There you have it.

Summary

By way of summary, we learned the required procedures of Linear Algebra including conventions, array types and operations such as finding the transpose, identity and inverse arrays. We also saw distinctions between addition and subtraction versus multiplication and division.

Walking through it in Excel helps us nail these down so we don't have to stare blankly at formula notation in text books. We also used array math practically in a spreadsheet with tips and tricks to speed things up.

Many people fear Linear Algebra and array math, but I think that is a mistake. This wasn't a complete review like you might find in a college course, but with what you saw here, you can make make most calculations involving big data and this offers a nice bridge to statistical programming languages. The sky is the limit after that.

Step 5 - Next: Portfolio Algebra

In the next episode we will put our learnings here to good use as we close out Chapter 5 by calculating portfolio return and portfolio variance. Matrix multiplication is all we need to simplify a calculation that looks very confusing in formula notation but is simply a matrix times two vectors. Piece of cake for us now.

If this is your first time here, we have hundreds of free tutorials on Finance and Technology made available for you without signing-up.

Please feel free to join us any time and have a nice day.


What's Next?

This tutorial in particular is helpful to watch in video as the Excel examples there are more clear.

Please joing our growing YouTube Channel audience. Subscribe straight from here.

  • To access all tutorials in Quant 101, click Outline.
  • For an introduction to Linear Algebra, click Back.
  • To use Linear Algebra for portfolio analysis, click Next.

Outline Back Next

~/ home  / finance  / quant 101  / array math



 
 
Keywords:
array math
matrix math
matrix multiplication
arrays in excel
inverse matrix
linear algebra operations
matrix rules
matrix algebra
matrix multiplication rules
excel array
matrix addition
matrix multiplication example
matrix mathematics
vector multiplication
scalar multiplication
matrix rules
matrix division
linear algebra basics
invert a matrix