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

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 • I think you may just be off by one -- right now, Ci has value AVG(B(i-3):B(i-1). What if you just did Ci=AVG(B(i-2):Bi) (i.e. move your C-column up by one step)? The choice of Period=2 in the screenshot you show might also indicate that you should use a 2-month moving average to replicate Google. Dec 30, 2019 at 21:09
• @cwindolf thank you so much. But how do they do the forecasting? SME forecasts into a plateau average which makes sense obv. since it's constantly averaging. What are they doing to make it forecast? Dec 30, 2019 at 21:21
• It looks like they just extend with a line, choosing the slope to be the same as the moving average curve's slope at the end of the data interval. Dec 30, 2019 at 21:27
• thank you so much again @cwindolf last question. how did you identify that their period is 2m not 3? is is experience/intuition, or is there an identifier? Dec 30, 2019 at 21:35
• It says 2 in the dropdown menu under Period in your second screenshot. But also, your data seems to have a general up/down alternating fluctuation that period 2 would get rid of, but period 3 would not. Dec 30, 2019 at 21:37

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