Scribd Logo
Upload

Welcome to Scribd!

  • 3 views

    Arrays

    Uploaded by

    vuongbichdung128

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd

    Arrays

    Uploaded by

    vuongbichdung128
    3 views10 pages

    Original Title

    arrays

    Copyright

    © © All Rights Reserved

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    3 views10 pages

    Arrays

    Uploaded by

    vuongbichdung128

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    You are on page 1of 10

    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,

    and

    Sumproduct(A,
    B)
    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 .
    Example

    and

    Sumproduct(A,
    B)
    Excel Method
    A B C D
    1 7 4 6
    2 5 2 11
    3
    4 9 3 8
    5 10 12 1
    6 =SUMPRODUCT(A1:C2,A4:C5)
    7 208
    8

    Applied Regression Analysis 2 Prof. Juran


    TRANSPOSE
    Definition
    For matrix A,

    Notes: If A is an r x c matrix, then and must be a c x r matrix. A does not


    need to be a square matrix.

    Example

    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
    A B C D E
    1 7 4 6
    2 5 2 11
    3
    4
    5
    6
    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)
    Press SHIFT+CTRL+ENTER, and curly brackets will appear round the
    formula (you can’t type them in).
    A B C D E
    1 7 4 6
    2 5 2 11
    3 =TRANSPOSE(A1:C2)
    4 7 5
    5 4 2
    6 6 11

    Applied Regression Analysis 4 Prof. Juran


    Multiplication
    =MMULT()
    Definition
    For matrices A and B,

    and

    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

    and

    Applied Regression Analysis 5 Prof. Juran


    Excel Method
    A B C D E
    1 7 4 6
    2 5 2 11
    3
    4 9 10
    5 3 12
    6 8 1
    7 =MMULT(A1:C2,A4:B6)
    8 123 124
    9 139 85
    Remember:
     Select the entire range A8:B9 before typing the formula.
     Press Shift+Ctrl+Enter.
    You can also get the same results using SUMPRODUCT:
    A B C D E F G H I
    1 7 4 6 9 3 8
    2 5 2 11 10 12 1
    3
    =TRANSPOSE(A4:B6)
    4 9 10
    5 3 12
    =SUMPRODUCT(A1:C1,E1:G1
    6 8 1 )
    7 Using SUMPRODUCTs =SUMPRODUCT(A1:C1,E2:G2)
    8 123 124
    9 139 85
    =SUMPRODUCT(A2:C2,E2:G2)
    10
    11 =SUMPRODUCT(A2:C2,E1:G1)
    12

    Applied Regression Analysis 6 Prof. Juran


    Inverse
    =MINVERSE()
    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:

    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 ) such that:

    Note:
    Not all square matrices can be inverted, a fact that has implications for
    regression analysis.
    Example

    If

    Then

    Because

    Applied Regression Analysis 7 Prof. Juran


    Excel Method
    A B C D E F G H
    1 check =MMULT(A2:B3,A5:B6)
    2 123 124 1 0
    3 139 85 0 1
    4
    5 -0.01254 0.01829 =MI NVERSE(A2:B3)
    6 0.02050 -0.01814
    Remember:
     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:
    A B C D E F G H I J
    1 Stock 1 Stock 2 Stock 3
    2 Mean return 0.140 0.110 0.100
    3 Variance of return 0.200 0.080 0.180
    4 StDev of return 0.447 0.283 0.424
    5
    6 Correlations Covariances
    7 Stock 1 Stock 2 Stock 3 Stock 1 Stock 2 Stock 3
    8 Stock 1 1.00 0.80 0.70 Stock 1 0.2000 0.1012 0.1328
    9 Stock 2 0.80 1.00 0.90 Stock 2 0.1012 0.0800 0.1080
    10 Stock 3 0.70 0.90 1.00 Stock 3 0.1328 0.1080 0.1800
    11
    12 Investment decision
    13 Stock 1 Stock 2 Stock 3 Total Required
    14 Fractions to invest 0.333 0.667 0.000 1 = 1
    15
    16 Expected portfolio return
    17 Actual Required
    =SUMPRODUCT(B2:D2,B14:D14)
    18 0.120 >= 0.120
    19
    =MMULT(B14:D14,MMULT(H8:J10,TRANSPOSE(B14:D14)))
    20 Portfolio variance 0.103
    =SQRT(B20)
    21 Portfolio stdev 0.321

    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:D1
    =B2*B14+C2*C14+D2*D14
    4)

    =0.14*0.333+0.11*0.667+0.1
    0*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:

    Applied Regression Analysis 9 Prof. Juran


    Recall that:
     The covariance of a random variable with itself is its variance. For
    example,
     The covariance of two random variables is independent of order. For
    example,
    Now the explicit calculation:
    MMULT(B14:D14,MMULT(H8:J10,TRANSPOSE(B14:D14)))
    (1)
    (2)

    (3)

    (4)

    (5)

    (6)
    (7)

    (8)
    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

    You might also like