Thank you Dr. Grande. The segement at timeline point 4:14 where you describe how to use Data Analysis Tool to create a "rolling average" was just what the doctor ordered. I have been interested in using this feature for a while. But on my own I could not come up with a "formula" which would accomplish that goal. And then I came across your offering. The instructions were direct, simple, and most importantly, accurate. My Excel offering didn't have the Data Analysis toolpack enabled. And as it had been a day or two since I first watched your video, and at that in just a single sitting, I struggled for a bit on my own trying to remember how to enable it. Then I said fluck it, went back to your video, and there it was. Now I can accomplish my goal.
Thanks for the great video, but I think this form of moving average produces a miss leading data set. In your examples the" moving average" calculated is 4 and 14 days latter than the true moving average. A true moving average relates to the average of the data over the averaging period, to the average date of the averaging period. (sorry, I could think of a better way to explain this). So in a 7 day moving average the first data point would be present on day 4 and for a 28 day moving average on the 14 the day. If you called it the average for the last 7 days, that would be correct, but it is not a true rolling average, it skews the results, in this case by making them appear later than the actually occur. You can do this by entering =SUM(B2:B8)/7 in the cell immediately to the right of day 4 ( assuming our data is in column B), and then rolling it down as far as needed, in the usual way, or you could use the average command, but I'm sure you know all that stuff. I became aware of this when studding the behaviour of fish, when I plotted fish behaviour against light levels. The fish behaviour was recorded every 6 mins over a period of several weeks, and the data was very noisy. To make sense of it I was using fairly long moving averages, often several hour. I noticed that the, behaviour patterns followed the light levels, but with a delay, which was dependent on the number of samples averaged ....... then it became obvious, that the delay was function of the analysis no the fish!
Please tell me why a simple moving average in excel does not go to the far right border? It stops (40 period)2 inches short of the last trade in.That is in a dynamic live data feed.
Dr. Grande, I have a question. i am trying to take make a 1 minute moving average with 1 second intervals on dynamically updating information which is pulled into excel 2007 with an RTD formula. Is this possible? Thank you, Ralph.
Thank you Dr. Grande. The segement at timeline point 4:14 where you describe how to use Data Analysis Tool to create a "rolling average" was just what the doctor ordered. I have been interested in using this feature for a while. But on my own I could not come up with a "formula" which would accomplish that goal.
And then I came across your offering. The instructions were direct, simple, and most importantly, accurate. My Excel offering didn't have the Data Analysis toolpack enabled. And as it had been a day or two since I first watched your video, and at that in just a single sitting, I struggled for a bit on my own trying to remember how to enable it. Then I said fluck it, went back to your video, and there it was.
Now I can accomplish my goal.
Very helpful. Straight to the point. Thank you!
Thanks - just what I was looking for tonight
very useful, many thanks for sharing it
Thanks for the great video, but I think this form of moving average produces a miss leading data set.
In your examples the" moving average" calculated is 4 and 14 days latter than the true moving average.
A true moving average relates to the average of the data over the averaging period, to the average date of the averaging period. (sorry, I could think of a better way to explain this).
So in a 7 day moving average the first data point would be present on day 4 and for a 28 day moving average on the 14 the day.
If you called it the average for the last 7 days, that would be correct, but it is not a true rolling average, it skews the results, in this case by making them appear later than the actually occur.
You can do this by entering =SUM(B2:B8)/7 in the cell immediately to the right of day 4 ( assuming our data is in column B), and then rolling it down as far as needed, in the usual way, or you could use the average command, but I'm sure you know all that stuff.
I became aware of this when studding the behaviour of fish, when I plotted fish behaviour against light levels. The fish behaviour was recorded every 6 mins over a period of several weeks, and the data was very noisy. To make sense of it I was using fairly long moving averages, often several hour. I noticed that the, behaviour patterns followed the light levels, but with a delay, which was dependent on the number of samples averaged ....... then it became obvious, that the delay was function of the analysis no the fish!
On what basis should we determine the interval to select (i.e. 7 day rolling average, 28 day rolling average)?
Thanx. Nicely explained and to the point.
I think this can be done with "moving average" 7 periods in trendline options...
Unfortunately, no. That will change the analysis from the TRENDLINE only. That does not reflect the changes of the raw data.
thank you sir
Very helpful. Thank you!
very useful, thank you
Well explained, thanks a lot mate
Thank you, quick guide and to the point :)
Thank you 🙏
Very nice, but i have one question. How can make data smoothing using Excel?
Please tell me why a simple moving average in excel does not go to the far right border?
It stops (40 period)2 inches short of the last trade in.That is in a dynamic live data feed.
Can you help me pls if event like event1 event2 for different locations then how to make graph for last 7 days avg graph
Dr. Grande, I have a question. i am trying to take make a 1 minute moving average with 1 second intervals on dynamically updating information which is pulled into excel 2007 with an RTD formula. Is this possible? Thank you, Ralph.
Nice explanation.
Thanks -
What is deference between moving average and rolling average?
Please anyone can define how to calculate forward and backward moving average in excel?
Useful for me
Thank you!
Thank you!
The functions needed to obtain the moving average seemed not as difficult.
Vielen Dank!
You're welcome - thanks for watching -
thxs you!
You are a G. holy fuck
lmaoo like im in an excel class and the dude ive been watching for 3 yrs just happens to have excel tutorials