+ Reply to Thread
Results 1 to 3 of 3

Need help with Moving Annual Total formula by Product, State, and Zip

  1. #1
    Registered User
    Join Date
    02-27-2012
    Location
    Salisbury, MD
    MS-Off Ver
    Excel 2007
    Posts
    2

    Need help with Moving Annual Total formula by Product, State, and Zip

    I am trying to enter a formula that will calculate a rolling 12 month annual total in column N of the attached spreadsheet. I need to work backwards to calculate the MAT beginning with the most current date in the spreadsheet rather than the oldest date. So I want to start with 2013-07 though 2012-08 and then calculate 2013-06 through 2012-05 and so on.Moving Annual Total spreadsheet.xlsxMoving Annual Total spreadsheet.xlsx The attached sample spreadsheet contains Products in column H, Sales in Column G, the 3 digit zip code in column J, and the month in Column L. For example, I need to figure out the 12 month rolling total for all Carnations in the state of NH within zip code "30" from 2013-05 through 2012-06. Then the next line should calculate the rolling 12 month total for Carnations in NH within zip code "30" from 2013-04 though 2012-05 and so on for each line in the spreadsheet.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Need help with Moving Annual Total formula by Product, State, and Zip

    Please see that attached. I've used a user defined formula (ALT F11 - Module 1 to see the code). It's a bit slow to calculate but gets there in the end.

    Note that this is now a macro enabled workbook.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-27-2012
    Location
    Salisbury, MD
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Need help with Moving Annual Total formula by Product, State, and Zip

    Thank you very much. That worked great! I really appreciate your quick response and solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Moving Annual Total
    By skate1991 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-05-2013, 01:58 PM
  2. Code/formula to maintain running total for annual accrual
    By Greed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-03-2012, 10:54 AM
  3. moving State Data
    By Lime in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2005, 02:35 PM
  4. Replies: 0
    Last Post: 08-29-2005, 11:04 AM
  5. how do i set uo total income and annual figures in excel?
    By blroses17 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-20-2005, 01:05 AM

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