ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBH
1
Matrix multiplication involves multiplying and summing elements of two matrices to produce a result matrix as such:
2
3
4
abef=ae + bgaf + bhMatrix multiplication on Wikipedia
5
cdghce + dgcf + dh
6
7
8
The result matrix has the same number of rows as the first matrix being multiplied, and the same number of columns as the second matrix being multiplied. Furthermore, the second matrix must have the same number of rows as the first matrix has columns.
9
10
So the following would be another example of a valid matrix multiplication, where a 3 x 2 matrix is multiplied by a 2 x 1 matrix:
11
12
13
abg=ag + bh
14
cd*hcg + dh
15
16
efeg + fh
17
18
19
20
Taking the above example, suppose the second matrix being multiplied consists entirely of 1's. This effectively simplifies the matrix multiplication to returning a sum of each row of the first matrix:
21
22
23
ab1=a*1 + b*1=a + b
24
cd*1c*1 + d*1c + d
25
26
efe*1 + f*1e + f
27
28
29
30
The "column of 1's" method is frequently used in Google Spreadsheets to build a MMULT array formula that performs calculations on rows down an entire column, which is especially useful when new rows are constantly being generated (for example, with Form submissions).
31
32
Say you wanted to build a formula that would sum each row in the array A1:C4. This can easily be done with, say:
33
=ArrayFormula(A1:A4+B1:B4+C1:C4)
34
35
However this becomes arduous if you are summing across numerous columns. Another way to do it is by performing matrix multiplication with the MMULT function:
36
=ArrayFormula(MMULT(A1:C4;TRANSPOSE(SIGN(COLUMN(A1:C1)))))
37
38
Breaking each part down, we can represent the A1:C4 part as:
39
40
41
A1B1C1
42
A2B2C2
43
A3B3C3
44
A4B4C4
45
46
47
The COLUMN(A1:C1) part is a 1x3 array which simplifies to {1,2,3}:
48
49
50
COLUMN(A1)COLUMN(B1)COLUMN(C1)=123
51
52
53
SIGN( ) will return 1 for any positive value, -1 for any negative value, and 0 for a zero value. So SIGN(COLUMN(A1:C1)) simplifies to {1,1,1}:
54
55
56
SIGN(1)SIGN(2)SIGN(3)=111
57
58
59
And TRANSPOSE( ) will simply transpose the array from 1x3 to 3x1, thereby creating our desired column of 1's, having the same number of rows as our first array (A1:C4) has columns. So TRANSPOSE(SIGN(COLUMN(A1:C1))) becomes:
60
61
62
1
63
1
64
1
65
66
67
Now when we apply the matrix multiplication of A1:C4 with the result of TRANSPOSE(SIGN(COLUMN(A1:C3))), we are left with an array that is the sum of each row in A1:C4:
68
69
70
A1B1C11A1*1+B1*1+C1*1A1+B1+C1
71
A2B2C2*1=A2*1+B2*1+C2*1=A2+B2+C2
72
A3B3C31A3*1+B3*3+C3*1A3+B3+C3
73
74
A4B4C4A4*1+B4*1+C4*1A4+B4+C4
75
76
77