+ Reply to Thread
Results 1 to 3 of 3

Extracting daily data from a 7 day rolling report that is delievered daily

  1. #1
    Registered User
    Join Date
    11-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    Extracting daily data from a 7 day rolling report that is delievered daily

    Hello all,

    Here is an interesting one. I am receiving a 7 day rolling report daily and need to find out what teh daily figure is. Hard to get your head around - so will explain another way every day, I receive a report that shows me the last 7 days of data aggregated - but no read out on what the number was yesterday. But becuase it is updated daily, I *Should* be able to get the daily figure form this data - but its not easy.....

    I've set up a simulated scenario - if anyone can help me, it would be fatnastic!

    See below (and attached with formulas) - the column simulated daily data is daily data (and the desired solution); the simulated 7 day rolling column is a sum of the last 7 days (for Day 7, its a sum of daily data from days 1-7). I need to be able to work out the aily data based ONLY on the data from the simulated 7 day rolling.

    Day simulated daily data simulated 7 day rolling
    Day 1 1
    Day 2 2
    Day 3 3
    Day 4 4
    Day 5 5
    Day 6 6
    Day 7 7 28
    Day 8 8 35
    Day 9 9 42
    Day 10 10 49
    Day 11 11 56
    Day 12 12 63
    Day 13 13 70
    Day 14 14 77
    Day 15 15 84
    Day 16 16 91
    Day 17 17 98
    Day 18 18 105
    Day 19 19 112
    Day 20 20 119
    Day 21 21 126
    Day 22 22 133
    Day 23 23 140

    Any thoughts would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-17-2010
    Location
    MN, USA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Extracting daily data from a 7 day rolling report that is delievered daily

    My math is much rustier than I'd like, so take this with a grain of salt.

    I don't think you can get the daily amounts just from the 7 day rolling amounts, unless you have a seven day interval of daily amounts already. If, somehow, you could get the first 7 days of daily data, then you could figure out the amounts by

    DailyValue8 = RollingTerm2-RollingTerm1 + dailyValue1

    You need the 7 seed values because the formula would need days: 8 &1, 9 &2 ... 14 & 7. On day 15 you'd be able to use day 8, which you've already calculated.

  3. #3
    Registered User
    Join Date
    11-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    Thumbs up Re: Extracting daily data from a 7 day rolling report that is delievered daily

    Many thanks for this - it makes a lot of sense and looking like it can get solved on teh back of this.

    Greatly appreciated!

+ 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