Search code examples
excelmathexcel-formuladata-analysis

Reverse a rolling total based on historic data


Say I have a list of rolling x-day page view totals. That is, each data point is the sum of the previous x days of page views, but I do not have each individual day's page view total. Would it be possible to get the individual values?

For example, say someone gathers the following page view metrics:

{4 days before Day 1: {1,2,3,8}, Day 1: 4, Day 2: 2, Day 3: 5, Day 4: 2, Day 5: 9, Day 6: 8, Day 7: 10, Day 8: 10, Day 9: 7, Day 10: 6}

They provide me with the following list of 5-day running totals:

{Day 1: 18 (1+2+3+8+4), Day 2: 19 (2+3+8+4+2), Day 3: 22 (3+8+4+2+5), Day 4: 21 (etc.), Day 5: 22, Day 6: 26, Day 7: 34, Day 8: 39, Day 9: 44, Day 10: 41}

Would it be possible for me to take only the second dataset and determine at least some of the values in the first dataset?


Solution

  • In your example, the history

    {1, 2, 3, 8, 4, 2, 5, 2, 9, 8, 10, 10, 7, 6}
    

    gives the following 5-day running totals:

    {18, 19, 22, 21, 22, 26, 34, 39, 44, 41}
    

    But so would the history:

    {3, 8, 1, 3, 3, 4, 11, 0, 4, 7, 12, 16, 5, 1}
    

    So no, in general you can't reconstruct any of the values.

    ...Unless you have five days in a row with no views, giving you a zero in the list of running totals. If that happens, you can reconstruct the entire history before and after.