I'm not sure if I can accurately describe my situation.
We have data (a starting value usually between 30000 and 50000) from four different sources, and each day the values for each source increases or decreases. Each day we calculate the increase or decrease percentage overall - we're not so interested in each dataset individually, rather the change across all of them together - and at the end of the week we also calculate the weekly increase or decrease in value (and monthly, and so on)
example
Day | Start Value | End Value | Pct Change |
---|---|---|---|
1 | 150000 | 153000 | 2.0% |
2 | 153000 | 154530 | 1.0% |
3 | 154530 | 148348.8 | -4.0% |
4 | 148348.8 | 152799.3 | 3.0% |
5 | 152799.3 | 150507.31 | -1.5% |
And at the end of the week we just use the difference between the start value and the end value to calculate the week's change (is this correct?)
This seems to work out alright, except that occasionally we add a new datasource and include its increase/decrease in the daily/weekly calcs. And these new sources don't always get added a the beginning or end of a week, so it makes calculating the weekly percentages a bit more complicated for us.
So for example if we added a new datasource with a start value of 30000 at the end of day 3
Day | Start Value | End Value | Pct Change |
---|---|---|---|
1 | 150000 | 153000 | 2.0% |
2 | 153000 | 154530 | 1.0% |
3 | 154530 | 148348.8 | -4.0% |
4 | 178348.8 | 183699.3 | 3.0% |
5 | 183699.3 | 180943.8 | -1.5% |
We can't just take the start and end difference, as we don't want to include the new source's start value, just the change after being added (along with the other change values)
How do we accurately calculate the weekly percentage change while allowing for a new dataset being added?
My boss says we should just average the start values and use the change total against that average... Change of 0.58%
My colleague thinks we should be able to just add the daily percentages... Change of 0.5%
And the way I was doing it was applying the daily change percentage to a base number, eg 100000
100000 + 2% = 102000
102000 + 1% = 103020
103020 - 4% = 98899.2
98899.2 + 3% = 101866.2
101866.2 - 1.5% = 100338.2
So from 100000 base to 100338.2 we'd have a weekly change of 0.3%
What would be the correct way to accurately calculate the weekly change here?
We're clearly not mathematicians, so after an accurate but understandable explanation if possible. If I understand the solution the result will be entered into a google sheets formula.