# Moving Average Trendline - Period Usage?

Copper Contributor

# Moving Average Trendline - Period Usage?

I am using a line chart that is graphing a set of numbers (XX.XX) for a 20-year time period - Specifically 01/01/2003 - 06/15/2023. I'm trying to add a trendline that will show the moving month over month average for this time period. However, I do not understand what I should enter in the "Period" field. My total data points equal 5157 (I think). I have 2 columns : A is the date, Column B contains the value on that date. I hope I provided enough information to receive an answer. Thank you in advance!!

2 Replies

# Re: Moving Average Trendline - Period Usage?

@KariMiller  wrote:  ``I'm trying to add a trendline that will show the moving month over month average for this time period. However, I do not understand what I should enter in the "Period" field. [....] I hope I provided enough information``

Not really.

First, "period" just refers to the number of "data points" to average.

If you think of the moving average as a window on the data that moves left to right, "period" is the width of the window.

I think you want a "monthly" moving average.  But we really cannot say how many data points comprise a "month", since that varies from month to month.

If you have stock data, and the data points are just trade days, there are typically 252 trade days in a year and 21 trade days in a month.

If your data is not trade days, it might help us to know how you pared down 7471 days between 1/1/2003 and 6/15/2023 to 5157.  IOW, what is your data comprised of?

-----

Second, that will not align with "month over month" data points.  But I don't think anything can, unless you vary the moving average "window" based on actual dates.

If you want to do that, you cannot use the chart trendline for that purpose.

Instead, I would do the moving average calculation in a parallel column.

And I would use a VBA procedure in order to handle the case where you don't have date for "one month later".

It is really quite messy.  For that reason, I would not recommend it.

# Re: Moving Average Trendline - Period Usage?

Yes, it is stock data, specifically the VIX over the last 20 years. It is listing the trade dates of each month along with the value. To come up with 5157, I used the count feature on the bottom toolbar of all the rows in my date column that were being included in my chart data. If I used your 21 trading days x 12 months x 20 years = 5,040. As you mentioned, give or take some days due to the length of different months, it sounds about right.

Thank you for explaining the moving average as a window. Helpful scenerio.