Daily spend vs projection spend pacing (%)

Question

I have a Google Doc sheet which currently has all our accounts spends. There are two tabs. The first tab details our actual daily spend for each account for each day of the month. The other tab is a projection tab, detailing the projected daily spend for each day of the month.

I'd like to have a row set in the daily spend tab, showing the actual daily spend pacing percentage against the projected on a daily basis. For example, projection tab shows spends for 1st Aug as $450 and 2nd Aug $300, however actual spend for both days is $800. I want to create a formula showing the pacing percentage against the projected spend. I also want this data to be updated automatically on a daily basis without me updating the formula.

Note: The projected daily spends are already updated till the end of the month. Each day has a different spend total.

Any ideas?

Answer 1

I would add another column to your projected sheet to calculate the running total for each day. You can paste the following into cell C2 and fill it down.

=If(Actual!B2<>ʺʺ,sum(Actual!B$2:B2)/sum(B2:B$2),ʺʺ)

Once that is done, you can put this formula anywhere you want to see the current rate.

=offset(C1,count(C:C),0)

You can hide column C if you don't want to see it.