How do I automate the YTD avg of previous yrs based on the # days in current yr

[Gungnir5]

How do I automate the YTD average of the previous years (i.e. 2008, 2009, 2010, 2011) to update as soon as I populate the data for the current year (i.e. 2012)? For example, as of June 22, 2012 we have 121 total trading days. How do I automate the average of 121 days in 2008, 2009, 2010 and 2011?


https://www.box.com/s/7d358910e12355004224

 

[NARAYANK991]

Hi ,


Can you check your file here , and verify whether the formulae are giving the correct results ?


https://docs.google.com/open?id=0B0KMpuzr3MTVdldoZzhiczRLUVE


You will have to download the file , and open it in Excel.


Narayan

 

[Gungnir5]

Sorry for the delay, but yes the formulae are ok.

I would like to calculate current YTD average for each category (ie interest rates, equities, fx, energy, commodities, metals) in the current year, and be able to compare the current YTD average with the YTD average of the previous 4 years.

I tried OFFSET, COUNT, Conditional functions, but mangled them so badly that I couldn't trust the output, which defeats the purpose of automation


Any help would be greatly appreciated!

 

[Montrey]

=sum(offset())/Count(offset())