Jun 22 2023 09:44 AM
Jun 22 2023 09:44 AM
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!!
Jun 22 2023 05:18 PM - edited Jun 22 2023 05:30 PM
@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``
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.
Jun 22 2023 05:31 PM
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.