Moving Average Value

[jdpro]

Using Mac, ver. 16.53

I have a data set with values in columns that I would like to use for calculating moving averages. I have the chart to go with the data set, and can add the element showing the moving average trend lines for each of the columns, but it doesn't seem to show the actual value of the moving average. I'm looking to create a 7-day moving average, showing the actual value.

Thank you as always,

Jdpro

 

[offthelip]

why not just use another column to caclulate the moving average. I do this quite a lot, and I like to be able to change the period for the moving average so I use this formula:
Assuming values in column A, put the number 7 in B1, then put his equation in B10 and copy down

=AVERAGE(OFFSET(A10,0,0,-$C$1))

Now change the value in B1 to 5 and you find all the moving averages change to a 5 day moving average. If you put a vaslue greater than 10 then the first few values give an error

 

[jdpro]

I think your $C$1 probably should be $B$1 if I'm putting the 7 there right? Looks good to me, thanks!

 

[offthelip]

Yes you are correct, $C$1 should be $B$1. sorry about that!! I copied it from a workseet where I had it in column C

 

[jdpro]

I found another offset option that works well too:
=AVERAGE(OFFSET(C2,0,0,-(MIN(ROW()-ROW($C$2)+1,7)),1))

This formula eliminates the errors by putting a height limit on the data range using the MIN function. Replace the 7 with your desired period for the moving average.