What Moving Average Formula does Google Sheets Use in Their Chart Trendline?

Question

I'm trying to come up with a formula that mimics the "Moving Average Trendline" that is provided in the Google Sheets Charts.

I've tried using a 3 month moving average, but it's not as good as Google's trendline.

Please check this sheet for a visual.

• mg is the real data
• the lime green line is Google Chart's trendline feature
• 3m SMA is my attempt at replicating their logic

Notice how much closer Google's trendline (lime green line) is to the series (mg) than the my formula (3m SMA) is.

here's a screenshot of the comparison between google and my function

and this how to find the trendline setting once you open up the chart settings

Answer 1

Thanks to @cwindolf for the main leg work on this.

This is how I was able to get it to be exactly the same as their trendline.

It consists of 3 parts.

1. the start value from the original data series
2. a 2 month moving average
3. forecasting using simple linear regression

1. Start value

We simply just use the same value from original the series

C0=B0

2. 2 month moving average

Average y values from the same index as the original series, and the index before it

Ci=AVERAGE(B(i-1):Bi) (thanks to @cwindolf)

3. forecasting using simple linear regression

We'll be using this formula to calculate our $y$

$$ y=\alpha +\beta x $$

where $\alpha$ is

$$ \alpha =\bar{y}-\beta \bar{x} $$

and $\beta$ is

$$ \beta=\frac{\sum(x_{i}-\bar{x})(y_{i}-\bar{y})}{\sum(x_{i}-\bar{x})^{2}} $$

Google Sheet's formula for C5's $\beta$ is =SUM(ARRAYFORMULA((A3:A4-AVERAGE(A3:A4))*(B3:B4-AVERAGE(B3:B4)))) / SUM(ARRAYFORMULA((A3:A4-AVERAGE(A3:A4))^4))

and the formula for C5's $\alpha$ with $\beta$ subbed in is =SUM(ARRAYFORMULA((A3:A4-AVERAGE(A3:A4))*(B3:B4-AVERAGE(B3:B4)))) / SUM(ARRAYFORMULA((A3:A4-AVERAGE(A3:A4))^2))

bringing it all together for C5($y$) we get

=AVERAGE(C3:C4)-(SUM(ARRAYFORMULA((A3:A4-AVERAGE(A3:A4))*(C3:C4-AVERAGE(C3:C4)))) / SUM(ARRAYFORMULA((A3:A4-AVERAGE(A3:A4))^2))*AVERAGE(A3:A4))+(SUM(ARRAYFORMULA((A3:A4-AVERAGE(A3:A4))*(C3:C4-AVERAGE(C3:C4)))) / SUM(ARRAYFORMULA((A3:A4-AVERAGE(A3:A4))^2))*A5)

To see it in action and to see a breakdown check out the SOLUTION sheet in this google sheet