Home

Help with an Excel formula Array

%3CLINGO-SUB%20id%3D%22lingo-sub-746543%22%20slang%3D%22en-US%22%3EHelp%20with%20an%20Excel%20formula%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746543%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%20everyone%20trying%20to%20get%20the%20portfolio%20variance%20using%20this%20formula%3C%2FP%3E%3CP%3E%3DMMULT(MMULT(TRANSPOSE(B9%3AE9)%2CB4%3AE7)%2CB9%3AE9)%3C%2FP%3E%3CP%3EB9%3AE9%20is%20the%20stocks%20individual%20weight%20%26amp%3B%20B4%3AE7%20is%20a%20variance-covariance%20matrix%26nbsp%3B%3C%2FP%3E%3CP%3Eits%20an%20array%20formula%20so%20im%20using%20cse%20but%20keep%20getting%20value%20error%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edoes%20anyone%20know%20what%20im%20missing%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-746543%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746601%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20an%20Excel%20formula%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F373790%22%20target%3D%22_blank%22%3E%40swans665%3C%2FA%3E%3C%2FP%3E%3CP%3EDo%20you%20want%20a%204x4%20array%20as%20the%20result%3F%20If%20so%2C%20move%20the%20TRANSPOSE.%3C%2FP%3E%3CP%3E%3DMMULT(TRANSPOSE(MMULT(B9%3AE9%2CB4%3AE7))%2CB9%3AE9)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20want%20a%20single%20value%20as%20the%20result%3F%20If%20so%2C%20move%20the%20TRANSPOSE%20to%20the%20second%20parameter%3A%3C%2FP%3E%3CP%3E%3DMMULT(MMULT(B9%3AE9%2CB4%3AE7)%2CTRANSPOSE(B9%3AE9))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20you%20multiply%20an%20array%20with%20a%20rows%20and%20b%20columns%20by%20one%20with%20c%20rows%20and%20d%20columns%2C%20b%20must%20equal%20c%20and%20the%20answer%20will%20be%20a%20rows%20by%20d%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-746610%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20an%20Excel%20formula%20Array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-746610%22%20slang%3D%22en-US%22%3E%3CP%3Ethank%20you%20very%20much%2C%20great%20advice%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F23287%22%20target%3D%22_blank%22%3E%40Brad%20Yundt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
swans665
New Contributor

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

2 Replies

@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.

thank you very much, great advice@Brad Yundt 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies