to calculate this month, this year, last year figure

**mingali** · 08-04-2010, 12:00 PM

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

**meyero90** · 08-04-2010, 12:23 PM

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** · 08-04-2010, 01:22 PM

this month actual = Aug 2010 figure
total for this year = sum for 2010
total for last year = sum for 2009

row 6 shows all the figures.

Thanks

**meyero90** · 08-04-2010, 02:15 PM

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.

**mingali** · 08-07-2010, 02:58 AM

Hi,

Thanks Meyero90. However, i need to stick to my current worksheet format as it is an official format.

Can anyone be able to help?

Thanks again

**DonkeyOte** · 08-07-2010, 03:10 AM

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.

**mingali** · 08-07-2010, 03:18 AM

Hi DO,

It should be based on last actual period not system date. normally, it is one month lagged, ie in August 2010, we are reporting July 2010 figures.

Thanks

**DonkeyOte** · 08-07-2010, 03:22 AM

FWIW - you can do the following:

Please Login or Register  to view this content.

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)