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?

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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?


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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

778 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