Using MMULT MMULT TRANSPOSE and getting #VALUE!

[tomtasticiii]

Hello Forum. This is my first time posting so forgive me if I make any errors.

First note, I have tried CTRL+SHFT+Enter and this did not solve my issue.

Second not, I am using Excel Professional Plus 2010

A little back ground. I have built a spread sheet to use for building ETF and Mutual Fund Portfolios. What I have built allows me to select a security by it's symbol and then all other information (price, yield, total return, etc.) auto populates from another sheet in the workbook. I wanted it to be automatic in filling in all data so the only things I need to select at the symbol and weightings.

Part of what populates and builds for me is a Correlation Matrix which shows these values by asset class. The sheet I built allows me to pick up to 60 different securities in the same or different asset classes. So basically it could built a correlation matrix that has a range BN12:DV72. If I only use 16 securities the cells that don't populate stay blank.

The Sheet auto populates risk of each security and then multiplies by the weighting to give me each securities variance. These individual variances are in the range BJ12:BJ72. Again if I only use 16 securities the the cells that do not populate stay blank.

Here is the problem. I am trying to figure the annual variance of the all securities chosen and I am using the following formula to do this:

{=MMULT(MMULT(TRANSPOSE(BJ12:BJ72),BN12:DV72),BJ12:BJ72)}

If I am only picking 16 securities and all cells that don't populate in the matrix or variance stay blank, the annual variance returns #VALUE !.

I have tried making all cells that do not populate returning 0 instead of being blank but still return the #VALUE ! in the annual variance.

The only thing that works to fix this is if I change the ranges of the Matrix and Variance to the cells that populate.

Please help! Thank you!!

 

[shg]

pick up to 60 different securities

Those ranges are 61 cells, not 60.

 

[tomtasticiii]

You are correct. I meant to put 61. Thank you.

 

[shg]

Works fine for me.

All the values have to be numeric. Try =COUNT(each range) to verify.

 

[tomtasticiii]

This might be a dumb question but how do I use the =COUNT( in the formula.

Also, I assume you used 0 in the cells that do not populate results?

 

[shg]

=count(BJ12:BJ72) should return 61

=count(BN12:DV72) should return 3721

I put =rand() in all the cells.

 

[tomtasticiii]

It is still not working. Perhaps the error is in the variance and/or correlation matrix formulas.

Correlation Matrix formula populates based on the symbol selected and populates in values from a sheet that has a built correlation matrix by asset classes. The formula is below.

=IF(ISNA(INDEX('Correlation Matrix'!$A$1:$N$14, MATCH(BM12, 'Correlation Matrix'!$A$1:$A$14,0), MATCH($BN$11, 'Correlation Matrix'!$A$1:$N$1,0))),"0", INDEX('Correlation Matrix'!$A$1:$A$14, MATCH(BM12, 'Correlation Matrix'!$A$1:$A$14,0),MATCH($BN$11, 'Correlation Matrix'!$A$1:$N$1,0)))

The Variance formula multiplies the Risk (Column BH) and the Weighting (Column F).

=(F12*BH12)

 

[shg]

Did you try the formulas I suggested?

 

[shg]

=IFERROR(INDEX('Correlation Matrix'!$A$1:$N$14, MATCH(BM12, 'Correlation Matrix'!$A$1:$A$14,0), MATCH($BN$11, 'Correlation Matrix'!$A$1:$N$1,0)), 0)

 

[tomtasticiii]

I did try to use the =COUNT AND =RAND() in the cells for the matrix but I don't think I used them correctly because it messed up the correlation matrix formulas.