to calculate this month, this year, last year figure

Hi

I have a report to contain data from 2009 to 2012. I need to calculate "this month actual", "Total for this year" and "Total for last year" in Coumn BD, BE & BF each.

Since there are more than two years data there, i would like to come up with a formula that it will update by itself (ie when 2011 comes, the formula will automatically link to 2011 data). Is it possible? if so, could someone please provide a solution?

Thanks

Hi

What types of calculations to you need to do (average, growth,....) and where do you need to generate them in Coumn BD, BE & BF. And these calculations should reference what data exactly (the one in row 6?)

Can you give me more details?

mingali,

I think you mainly need to think "out of the box", I tried to re-shape your data, and presented them differently in order to obtain what you requested. Look to the end right of your spreadsheet, I've included my changes and ideas and hoping this will help you. There would be some minimal manual entries, BUT nothing can be entirely automated .

Let me know if you have any comments.

I think you need to be more specific in terms of what determines the current month.

You state rather confusingly that the figure in BD should be last "actual" yet state that based on sample this figure should be for Aug-10 which is denoted as Forecast rather than Actual (ie there is no Actual figure).

So, should current month based based upon system date or should it in fact be based on the last "actual" period ?

It is more common in reporting to base this type of analysis on the last actual period rather than system date.

FWIW - you can do the following:

A3: =LOOKUP(2,1/($D$5:$BC$5="Actual"),$D$4:$BC$4)
assumes you're looking for last Actual

BD6:
=INDEX($D6:$BC6,MATCH($A$3,$D$4:$BC$4,0))

BE6:
=INDEX($D6:$BC6,MATCH(YEAR($A$3)&"",$D$5:$BC$5,0))

BF6:
=INDEX($D6:$BC6,MATCH(YEAR($A$3)-1&"",$D$5:$BC$5,0))

If you want to use System Date rather than last Actual period then alter A3 formula accordingly but note you are mixing the logic of your month row so you will need to adjust accordingly.
you stipulate first month as 1st Jan then use EOMONTH for subsequent periods in fiscal year - ie 28th Feb - you would be better off using EDATE for consistency or use 0 increment and +1 to result (ie all 1st of month)