1
$\begingroup$

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

comparison between google and my function

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

how to find the trendline setting

$\endgroup$
5
  • 1
    $\begingroup$ 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. $\endgroup$
    – mostsquares
    Commented Dec 30, 2019 at 21:09
  • $\begingroup$ @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? $\endgroup$
    – vvMINOvv
    Commented Dec 30, 2019 at 21:21
  • 1
    $\begingroup$ 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. $\endgroup$
    – mostsquares
    Commented Dec 30, 2019 at 21:27
  • $\begingroup$ 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? $\endgroup$
    – vvMINOvv
    Commented Dec 30, 2019 at 21:35
  • 1
    $\begingroup$ 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. $\endgroup$
    – mostsquares
    Commented Dec 30, 2019 at 21:37

1 Answer 1

Reset to default
2
$\begingroup$

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

$\endgroup$

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .