Google Sheets How to
Last updated: May 26th, 2023
Alex Black
Co-Founder / CEO
In this tutorial we’ll look at how to use array formulas to calculate your daily change in MRR and a moving average of the daily change to smooth out the noise. This applies equally to other daily numbers you may wish to analyze such as Sales, traffic, etc. Moving averages are a great way to smooth out daily noise to give you a better look at your trend lines.
Quick reminder that SyncWith is laser focused on helping business users solve their data problems in Google Sheets by making data from any API instantly available and sync’d. Give us a try if you’re not already
Analyzing the moving average of the daily change helps you understand whether you’re growing linearly or accelerating your growth. Because you can see how much new MRR you’re adding. Ideally the daily MRR you add each day has an increasing slope. Again equally applicable to analyzing website traffic, revenue, retention, etc.
If you’re running a SaaS business you’re likely tracking your MRR, a long with a tonne of other metrics. Likely you use Stripe to handle payment and they have an excellent api to pull in all the information you need to create a stunning dashboard tracking retention, revenue, etc.
Let’s suppose we are pulling our Daily MRR from Stripe API into Google Sheets and we want to understand how much progress we make each day in terms of adding new MRR and what are trends are for adding incremental MRR each day. The first thing we need to do is add a column showing us the detla of new revenue added each day.
To do this we can use an arrayformula arrayformula(if(B4:B="",,B4:B-{B4;B4:B})), what this does is subtract the current day from the previous day, for the first entry it subtracts it by itself since we don’t know what the previous MRR was. Breaking it down
The daily moving average is the set of simple moving averages for all the days in the set of data.
To calculate the simple moving average for a given day we add up all the numbers over the time period and divide by the number of days (arithmetic mean over the time period).
You do this for every day and then you get a time series where the number on a given day is the average of the previous x days which smooths out the value on a given day because it’s just the average of the previous period.
Common periods you might use:
So how to do this with an ARRAY FORMULA in Google Sheets? Using SumIFs. One of the annoying things about Sheets is many of the formulas are not available as array operations, so you have to get creative with the formulas that are available. But the concept is this: A single formula to calculate the moving average for every row in a column.
Now we can graph our MRR over time with the MRR delta and the MRR Delta 14 DMA: