Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Thanked 0 Times in 0 Posts

    Rolling Calculation (2007)

    I have a forecast that I am trying to sum individual monthly totals. The forecast has numbers in each month Jan - Dec. I have a cell that represents YTD forecast numbers that I am having to change each month to pick up the next forecasted months value.

    Example- Its November, my YTD forecast number would be the sum of Jan-Nov. Next month, I would like the formula to automatically recognize that it is now December and extend the range of the formula to pick up Jan-Dec numbers.

    Right now, I go into the first cell of the table, extend/change the formula, and then paste it down the page.... THERE HAS GOT TO BE A BETTER WAY!

    I have included a small portion of the file. See cell C7 for the current formula.

    As always, your help and input is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Rolling Calculation (2007)

    In the attached version, I entered 01/01/08 in G6, 02/01/08 in H6, then selected G6:H6 and filled right to R6.
    Next, I changed the number format to the custom format mmm so that only the abbreviated month name is displayed.
    I changed the formula in C7 to

    and filled down to C9.
    When you change the date in H1, the results will be updated automatically.
    If you always want to refer to the current month, enter the formula =TODAY() in F1.
    Attached Files Attached Files

Posting Permissions

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