Forum Discussion

swans665's avatar
swans665
Copper Contributor
Jul 10, 2019

Help with an Excel formula Array

hi everyone trying to get the portfolio variance using this formula

=MMULT(MMULT(TRANSPOSE(B9:E9),B4:E7),B9:E9)

B9:E9 is the stocks individual weight & B4:E7 is a variance-covariance matrix 

its an array formula so im using cse but keep getting value error

 

does anyone know what im missing?

 

Thanks

Formulas and Functions

2 Replies

  • Brad Yundt's avatar
    Brad Yundt
    MVP
    Jul 10, 2019

    swans665

    Do you want a 4x4 array as the result? If so, move the TRANSPOSE.

    =MMULT(TRANSPOSE(MMULT(B9:E9,B4:E7)),B9:E9)

     

    Do you want a single value as the result? If so, move the TRANSPOSE to the second parameter:

    =MMULT(MMULT(B9:E9,B4:E7),TRANSPOSE(B9:E9))

     

    When you multiply an array with a rows and b columns by one with c rows and d columns, b must equal c and the answer will be a rows by d columns.

Resources