Dynamically updating ranges in Excel - dlmille please update my spreadsheet (2)

Posted on 2011-03-08
Last Modified: 2012-05-11
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

Thanks again.

Question by:hedgeselect
  • 7
  • 5
LVL 41

Expert Comment

ID: 35085053
Its ok to give me a heads up that a new question is coming, but you might want to make your questions a bit less directed as other E-E experts may want to contribute.  And by clicking ASK RELATED QUESTION you give everyone that heads up - Just Click ASK A RELATED QUESTION at the bottom of a current question and I and any E-E experts that are following will be directed there.

On to the question at hand...

1.  I assume the standard deviation includes all entries that have data - e.g., from when you started tracking fund data to that point in time, correct?  I created the range fundStart to know what row the actual tracking begins on and STDEV(fundStart:currentRow) is the way to go.

2.  12 month annualized volatility is the same, except for partial year at the beginning - and I use fundStart to get that.  If current beginning of year > fundstart, just use last 12 months, otherwise fundstart:current row as with above.

3.  I created a new range called lastFundDEC for the last valid datapoint and lastFundDateDec for the corresponding date (you're really testing my offset function skills - this is great - I need to use this more!) which captures the last valid entry of December.

for example - lastFundDec = =OFFSET('Fund Data'!$L$1,MATCH(99^99,'Fund Data'!$L:$L)-1,0)

and lastFundDateDec = same formula, but looking at the Column B range.  Its important to use absolute addressing for these types of formulas or things go haywire.  E.g., $L:$L or $L$1 as opposed to L:L or L1.

Finally, I created a named range called lastMonthlyYTDReturn -to pick that up for partial years.  I check the year label and if its not a number, I'm assuming you're wanting the monthly YTD number for the last return

Please see attached and advise,


Author Comment

ID: 35085674
Sure, will do going forward.  

I just wiped all the monthly fund data and replaced it with the following monthly returns which I placed in different time periods in column C, and I get the #REF! error msg.  

Does that happen to you?
LVL 41

Expert Comment

ID: 35085689
How did you wipe the data?  Did you delete rows, or just erase the data with the delete key or CLEAR?

LVL 41

Expert Comment

ID: 35085693
Where did you place (what rows) the data?
LVL 41

Expert Comment

ID: 35085792
I just cleared the monthly data and pasted it to the end for the latest dates...

see attached...


Author Comment

ID: 35086650
I just erased the data with the delete key.  Ok I think I have found the issue.  It seems to happen when I copy and paste numbers into the cell, but if I paste it as VALUES, its ok.

Use the same new numbers I pasted above, the yearly return column E does not seem to compute?  Could you check the yearly return when the data is pasted into 31 Jan 2011.  Its showing the yearly return as 0%.

Also just a thought, but where there is only fresh 12 months of data, shouldn't the monthly YTD returns match the fund cumulative performance of $1 ivnested at inception?  It shows 1.084 i.e. 8.4% performance for the year v 9.56% for the monthly YTD?  Same with annualised return, shouldn't the Dec figure match the monthly YTD return as the final figure for the year should close everything off and make all yearly figures more conclusive if you know what I mean?


IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

LVL 41

Expert Comment

ID: 35086737
Ill check later this PM and do some testing so we can make this more bulletproof

LVL 41

Expert Comment

ID: 35089487
Ahhh yes - if there are prior returns, I use the previous december as a calibration point to set back to $1 by dividing by that column J value.  But, if there are not, I don't need to do that.  Corrected...  I updated Quarterly returns as well along the same lines...

As far as the column F I think we have a begining of period versus end of period issue with the disconnect.  At some point I started thinking end of period.

So the January $1 value is not $1, its $1 at the cumulative return for January (since we're testing on Jan 31st).  I'm not sure I ever played with Column F, but reverting back to the original original question, January's $1 value is end of month, not beginning of month figure, so I changed that :)

see attached.


Author Comment

ID: 35091817

I think we're slowly getting there.  Yes all these values at end of month values, not beginning of month.  I should made that clear at the beginning.  

The first 12-months of rolling returns (column I) need to show the rolling % as the single digit i.e. 1.03% rather than 101.03%.

The formulas have begun to get too complicated for me to follow so I will need to check sample data that I can compare against results from other fund performance templates or magazines etc.  This might take a few days to dig out.  

I need to remind myself and check whether annualised returns (col F) should be the same as yearly returns (col E) when end of December comes.  Could you just confirm whether the annualised returns formula you are using is based on the calculation of "all the monthly returns for that year, (including if there is only a partially completed year), then averaged and then multiplied by 12" to get annualised or is it based on something else?  Because if you had all 12-months figures, divided by the number of months of data for that year i.e. 12, and then multiplied it by 12 again this should match the yearly return?

I think col C in the fund summary table (worksheet 3) needs to be checked C21, C22, C25 as col D&E on the fund summary table contains a % return when there is no monthly data for that period yet.  In fact, can we show it so that no rows show data until the appropiate condition i.e. monthly data is present?

I've just added some more  monthly returns for an example.   Thanks again.  

LVL 41

Accepted Solution

dlmille earned 500 total points
ID: 35097783
I corrected column I

I've not looked at Annualised return - not my formula.  

Take $1 invested in January with 10% return Jan thru Dec.  Your calculation for Annualized return shows 120% in each month.  However, it does not account for compounding.

With compounding, if I have $1 invested with these returns, by December, I'll have $3.14 by the end of the year - so the return for the year would be 213%.

Annualized return should take the compounded return on a YTD basis, then divide by # months to that point and multiply by 12. See revised formula in column M orange shaded.

If you want calcs in the table to work correctly, you need to ensure your data entry is up to date.  I hit delete key on future data and the numbers in the summary table come back in line (re: dynamic ranges based on last entries!).




Author Comment

ID: 35098110
Hi Dave,

I've posted the question again (to repair the file) on

I'll close this one as you've done a fair bit.  Please feel free to help correct the various errors including Annualised Return.  I just realised the other person who did this on EE has cocked up a few of the columns and I didnt check his work.


Author Closing Comment

ID: 35098677
Thanks, please use latest EE question to update anything from this q.

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now