-2

I've set a Trendline for a Chart Series to Moving Average, but the Period only accepts a maximum of 10 periods.

The data is provided as Weekly Stock Prices, using GOOGLEFINANCE(), for the last 10 years.

I would like to see a trendline over a longer period, such as 26 (weeks). Ideally, I would specify this Period in a sheet cell, so I can change it depending on the stock I'm interested in.

One option I thought might work is to add an extra column that works out an average, and use that as another Series? However I don't know how to specify the number of Periods in such a calculated Average().

Is there any method to do this?

maxhugen
  • 1,870
  • 4
  • 22
  • 44
  • Hi there, I can help you do this! don't worry about the forum police. The way to do it is going to be to build an auto-generated extra column of data. (rather than relying on internal "trendline" type functionality. However, it's going to be hard to help without some sample data. Here's a blank you could paste into and I could demo how to generate the data (as well as the trendline) https://docs.google.com/spreadsheets/d/1MUogcZWIIF8x_O6TKXYqb5xsSW9apB5EqRwQrQsRW14/edit – MattKing Apr 08 '21 at 21:30
  • Thanks mate, I did find the function OFFSET() that let me grab the last "x" cells and thus create a column of moving averages. Cheers, Max. – maxhugen Apr 09 '21 at 05:45

1 Answers1

1

I finally found a function that allowed me to create a moving average, and used this for a third column which created another Series in the Chart. It is OFFSET(). This function can define a range which is relative to a specific cell. So the formula n cell C52 is :

OFFSET(B52,-($G$1-1),0,$G$1,1) Gets the range of previous cells for the avg, using cell G1 to vary the period required

AVERAGE(OFFSET(B52,-($G$1-1),0,$G$1,1)) Returns the average

=IFERROR(AVERAGE(OFFSET(B52,-($G$1-1),0,$G$1,1)), "") Error handler as the first (G2-1) cells can't return a range

enter image description here

Cell A2 gets the stock data for the last 10 years using:

=GOOGLEFINANCE(A1, "price", EDATE(TODAY(),-12*$E$1), TODAY(), "WEEKLY")

maxhugen
  • 1,870
  • 4
  • 22
  • 44