+ Reply to Thread
Results 1 to 8 of 8

to calculate this month, this year, last year figure

  1. #1
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2010 (office) 2013 (home)
    Posts
    255

    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
    Attached Files Attached Files
    Last edited by mingali; 08-07-2010 at 04:59 AM.

  2. #2
    Forum Contributor meyero90's Avatar
    Join Date
    03-24-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    202

    Re: to calculate this month, this year, last year figure

    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?
    If I helped, Don't forget to add to my reputation (click on the little scale)
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)

  3. #3
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2010 (office) 2013 (home)
    Posts
    255

    Re: to calculate this month, this year, last year figure

    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

  4. #4
    Forum Contributor meyero90's Avatar
    Join Date
    03-24-2010
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    202

    Re: to calculate this month, this year, last year figure

    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.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2010 (office) 2013 (home)
    Posts
    255

    Re: to calculate this month, this year, last year figure

    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

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: to calculate this month, this year, last year figure

    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.

  7. #7
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2010 (office) 2013 (home)
    Posts
    255

    Re: to calculate this month, this year, last year figure

    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

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: to calculate this month, this year, last year figure

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1