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

Posted on 2011-03-07
Last Modified: 2012-06-21
Dave (dlmille),

Further to my last question, could you please update the previous spreadsheet you created with the following:

1.      Two new columns in worksheet 1 (Fund Data worksheet), perhaps between existing columns E and F which look at a) Year To Date (YTD) returns and b) the other column to have 12-month rolling returns (both based on the monthly fund data).

Just to be clear, the YTD returns will always show the cumulative return in that year, so for 2011, it will be Jan 11 and Feb 11.  For complete years the YTD will match the Yearly returns.  The rolling returns will fill every cell in the new column and also look at the cumulative returns for exactly a 12-month period regardless of what the current month is.

As with the rolling 12-month chart from the other question, are you able to include that chart in this spreadsheet here such that it is a second chart in the third worksheet?

Thanks again.
Question by:hedgeselect
  • 3
  • 3

Author Comment

ID: 35066254
BTW, on the last spreadsheet, the new monthly YTD column does correct match the first month's return for Jan of each, but does not march the Yearly Return column (for December) in each year which it should as the cumulative return for the end of each year should be the same as the YTD for December.

I think, when you moved over to my worksheet with the Monthly, Quarterly and Yearly figures, the spreadsheet was based on someone else's calculation method and not yours.  I dont know if this is what is creating the problem?  Do you mind checking against the formulas you created (the one where I split it 400/100 with the other guy)?

LVL 41

Accepted Solution

dlmille earned 500 total points
ID: 35066962
I believe I've got it corrected, leveraging the learnings from the first example we did together way back when :)

Monthly YTD was corect, but the formulas for Quarterly and Yearly needed to be recalc'd - can't just sum the monthlies as we well know.

Basically for a period return, you take:

(CURRENT value of $1 invested MINUS value of $ invested just before the period starts)
divided by the $ invested just before the period starts....

For a YTD, the return is merely the cumulated return to that point in the year - Monthly YTD return does that.

And for 12 month rolling return, you take:

CURRENT value of $1 invested MINUS value of $ invested just before the period starts.

Let me know if this works for you.

Also this cleaned up the green formula "errors" as they are now consistent, I believe.

See attached...

LVL 41

Expert Comment

ID: 35066968
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.


Author Comment

ID: 35072636
Hi Dave,

Other question now closed and I will close this one shortly but open another in a few hours when I get home.  This seems to be better working now after your last update i.e. YTD returns.  

For another new EE question (to be posted later this evening) if you want to work on the below?:

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

Hope that is clear?  Happy to split this over several EE questions if it helps?  Unfortunately I cannot upload a marked up spreadsheet whilst in the office otherwise I would have done the basic column headers etc.

Thx again.
LVL 41

Expert Comment

ID: 35078049
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

Make sense?

Ping me when you want to review...


Author Comment

ID: 35079333
Hi Dave

All your assumptions seem correct, although I would need to verify it on the spreadsheet.

Could you please post the latest spreadsheet with the updates on

I will close this q once you have moved over to the other EE q.


Featured Post

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!

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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 simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

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

21 Experts available now in Live!

Get 1:1 Help Now