Dynamically updating ranges in Excel - dlmille please update my spreadsheet (2)
Posted on 2011-03-08
Hi Dave (dlmille),
Further to my last question, could you please update the latest previous spreadsheet you created with the following:
1. New column K in Fund Data worksheet, called Annualised Volatility which is simply the annualised standard deviation of monthly returns which is recorded on a monthly basis for all inclusive monthly data. For example, in your last spreadsheet this would fill every cell between K152 and K227 (but will work on the whole column when data is added). See the formula in cell C24 if necessary in worksheet 3.
2. New column L titled "12-month Annualised Volatility" which does the same as (1) above, except that the standard deviation of monthly returns is based on that year's returns only and is calculated and displayed in the December row
3. For the summary table, can we:
a) insert a new row 24 criteria titled "12-month Annualised Volatility for [XXXX]" in which take the latest annual annualised volatility figure the latest year and [XXXX] is dynamically replaced with the last year. Where it is a new fund or no previous year exists, then a "-" can be added
b) have the last 3-years annual returns dynamically update in the table under the respective cell (where there are 3-years of returns) otherwise "n/a" to be inserted in the cell and also the table automatically updates so that if there was e.g. 8 years of data, only the last 3 years would show
Given that this is a multiple question set, if you are able to post one spreadsheet reflecting all the above, and when verified, I will open 3 new EE questions to reflect questions 2, 3a and 3b as individual solutions.
In answer to your previous questions:
1. Yes, sounds about right, but difficult to verify without the updated spreadsheet
2. Sounds right
3. Again sounds right, but easier for me to verify once I can see the spreadsheet