tomtasticiii
New Member
- Joined
- Oct 15, 2017
- Messages
- 8
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!!
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!!
