Introduction to Excel Array Functions

Introduction to Excel Array Functions

This document is intended to introduce the use of Excel to perform basic matrix algebra operations.

Sumproduct

Definition For matrices A and B,

a11a21Aar1a12a22ar2a1ca2carcb11b21 and Bbi1b12b22bi2b1jb2j bijSumproduct(A, B) a11b11a12b12a13b13arcbij Notes:  r = i and c = j. In other words, the two matrices must have the same number of rows as each other and the same number of columns as each other.  They do not need to be square matrices (where r = c and i = j).

 Algebra geeks sometimes call this operation the “dot product” (to distinguish it from matrix multiplication — described later in this document), and symbolize it as AB. Example 7A5426119 and B103128 1Sumproduct(A, B) a11b11a12b12a13b13arcbij

794368111 208

Excel Method 12345691031281A75B42C611D=SUMPRODUCT(A1:C2,A4:C5)72088 Applied Regression Analysis 2 Prof. Juran

Transpose

Definition For matrix A,

a11a21Aar1a12a22ar2a1ca2carc

a11a12a1ca21a22a2car1ar2arcAT

Notes: If A is an r x c matrix, then and AT must be a c x r matrix. A does not need to be a square matrix. Example 7A5T426115211

A746

Applied Regression Analysis 3 Prof. Juran

Excel Method There is an Excel function for this purpose, called TRANSPOSE. This function is one of a special class of functions called array functions. In contrast with most other Excel functions, array functions have two important differences:  They are entered into ranges of cells, not single cells  You enter them by pressing Shift+Ctrl+Enter, not just Enter

12345A75B42C611DE Using the spreadsheet above as an example, we start by selecting the entire range A4:C6. Then type into the formula bar =TRANSPOSE(A1:C2)

6Press Shift+Ctrl+Enter, and curly brackets will appear round the formula (you can’t type them in).

1234567465211A75B42C611=TRANSPOSE(A1:C2)DE

Applied Regression Analysis 4 Prof. Juran

Multiplication

Definition For matrices A and B,

a11a21Aar1a12a22ar2a1ca2carcb11b21 and Bbi1b12b22bi2b1jb2j bija11b1ja12b2ja1cbija21b1ja22b2ja2cbij

ar1b1jar2b2jarcbija11b11a12b21a1cbi1ababab211122212ci1ABabar2b21arcbi1r111a11b12a12b22a1cbi2a21b12a22b22a2cbi2ar1b12ar2b22arcbi2Notes:  It is conventional to describe the shape of a matrix by listing the number of rows first, and the number of columns second. Matrix A above is an r x c matrix, and matrix B is an i x j matrix.  In this operation, it is necessary for c = i. However it is not necessary for r = j. In other words, B must have the same number of rows as A has columns, but it is not necessary for B to have the same number of columns as A has rows.  The product AB will always be an r x j matrix. Example 7A54261193 and B81012 1

AB

79436859231181231391248571041261510212111

Applied Regression Analysis 5 Prof. Juran

Excel Method 1234567891231391248593810121=MMULT(A1:C2,A4:B6)A75B42C611DE Remember:

 Select the entire range A8:B9 before typing the formula.  Press Shift+Ctrl+Enter.

You can also get the same results using SUMPRODUCT:

123456789101112=SUMPRODUCT(A2:C2,E1:G1)A75938B4210121C611DE910F312G81HI=TRANSPOSE(A4:B6)=SUMPRODUCT(A1:C1,E1:G1)Using SUMPRODUCTs12313912485=SUMPRODUCT(A1:C1,E2:G2)=SUMPRODUCT(A2:C2,E2:G2) Applied Regression Analysis 6 Prof. Juran

Inverse

Definition First, define a square matrix Ij as a matrix with j rows and j columns, completely filled with zeroes, except for ones on the diagonal:

10I001000 1This special matrix is called the identity matrix.

Now, for a square matrix A with j rows and j columns, there may exist a matrix called A-inverse (symbolized A1) such that:

AA1 Ij

Note: Not all square matrices can be inverted, a fact that has implications for regression analysis. Example If A139Then A1123124 850.01829

0.018140.012540.02050Because

AA1

1230.012541240.020501390.01254850.020501230.018291240.01814

1390.01829850.01814100 1 I2

Applied Regression Analysis 7 Prof. Juran

Excel Method A1BCDcheckEFGH=MMULT(A2:B3,A5:B6)2123124103139850145-0.012540.01829=MINVERSE(A2:B3)60.02050-0.01814Remember:

 Select the entire range A5:B6 before typing the formula.  Press Shift+Ctrl+Enter.

Applied Regression Analysis 8 Prof. Juran

Appendix: Application to Financial Portfolios

Here is a spreadsheet model of a three-stock portfolio optimization problem:

A123456789101112131415161718192021Portfolio variancePortfolio stdevExpected portfolio returnActual0.1200.1030.321Required=SUMPRODUCT(B2:D2,B14:D14)>=0.120=MMULT(B14:D14,MMULT(H8:J10,TRANSPOSE(B14:D14)))=SQRT(B20)Investment decisionStock 1Fractions to invest0.333Stock 20.667Stock 30.000Total1=Required1Stock 1Stock 2Stock 3CorrelationsStock 11.000.800.70Stock 20.801.000.90Stock 30.700.901.00Stock 1Stock 2Stock 3CovariancesStock 10.20000.10120.1328Stock 20.10120.08000.1080Stock 30.13280.10800.1800Mean returnVariance of returnStDev of returnBStock 10.1400.2000.447CStock 20.1100.0800.283DStock 30.1000.1800.424EFGHIJ In cell B18 the SUMPRODUCT function is used to calculate the expected return on the

portfolio. The expected return is a function of (a) the expected returns on the three stocks and (b) the portfolio weights (fractions to invest). To be explicit:

SUMPRODUCT(B2:D2,B14:D14) =B2*B14+C2*C14+D2*D14

1x12x23x3

=0.14*0.333+0.11*0.667+0.10*0.000 =0.120

The portfolio weights are decision variables in this problem; if these change, then of course the expected return on the portfolio would also change.

In cell B20, the MMULT and TRANSPOSE functions are combined to calculate the variance of the portfolio.

For notational purposes, let’s define two matrices:

Ax1COVBCOVCOVx2112131x30.333COVCOVCOV1222320.667130.000

0.10120.08000.10800.13280.1080

0.18000.2000COV230.1012COV330.1328COVApplied Regression Analysis 9 Prof. Juran

Recall that:

 The covariance of a random variable with itself is its variance. For example,

2COV111  The covariance of two random variables is independent of order. For example,

COV13COV31 Now the explicit calculation:

MMULT(B14:D14,MMULT(H8:J10,TRANSPOSE(B14:D14)))

ABAT (1) (2)



COVx3COVCOVCOVx3COVCOV112131112131COVCOVCOV122232x1x2x1COV23x2COV33x31312COV

(3)

(4)

x1x2x3xCOV22x2COV23x3211xCOV32x2COV33x331111131222x1COVx2COVx1COVx2COVx3COVx1x1COVx1x2COVx1X3COVx2x1COVx2x2COVx2x3COV1323x3x3x333

(5) (6)

(7) (8)

3233x1COVx1COV221112x1x2COVx2x2COV2122x2x3COV23x332x1x2COV12x3x3COV23x2x1COV1313x3x3COV31x1

1x12x23x32x1x2COV22222x2x3COV222x1x3COV

0.4470.33320.333220.2830.667220.4240.0000.6670.101220.6670.0000.108020.3330.0000.13280.10275

This calculation is usually presented in the form of step (6) in introductory statistics

classes, so as to avoid frightening people with the more general matrix notation.

Applied Regression Analysis 10 Prof. Juran