Dynamic Offset Average

[Gesyca_is_joy]

I have a table in which I constantly add rows. I have a column in which i want to dynamically calculate the average of the past XX months.

Right now I am using the following formula in Column C for a 5 month average.

=Average(Offset(A1,COUNT(A2:A11),0,-5)

However it's not quite what I want because what i am looking for is a formula that calcuates the average for the previous 5 rows based on each cell...so row 6 would average rows 1-5, and row 7 would average rows 2-6 and so on.


[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]Number[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]21[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]22[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gesyca_is_joy]

YAY thank you so much!! That works, even as I change the value in F1 and add more rows... thank you!

Now, will you please educate me on what this formula is doing so I can learn? I am well familiar with IF, COUNT and AVERAGE, but the OFFSET is what i’m Confused about. I can see the formula is counting the values in the Actuals column, returning a blank if they don’t comply with the criteria in F1 and averaging the values if they do, but what is (OFFSET([@Actuals],-$F$1,,$F$1,) actually saying? I looked it up on Google a bunch before I came to this forum but I can’t seem to wrap my head around it for some reason.

Hi,

RollingAverage: =IF(COUNT(OFFSET([@Actuals],-$F$1,,$F$1,))<>$F$1,"",AVERAGE(OFFSET([@Actuals],-$F$1,,$F$1,)))


CTRL+SHIFT+Enter




Greetings



Christian

 

[Besserwisser]

Hi,

AVERAGE(OFFSET([@Actuals],-$F$1,,$F$1,) says:

go from the actual row in Column Actuals x rows up (-$F$1), from this cell take a Heigh with x ($F$1)

for i.e.: the actual row is row 5 in column Actuals, F1=4, so you go up 4 rows, you are in row 1, take a Heigh with 4 Rows (=row 1,2,3,4 from the column Actuals).

Hope, my explanation will help you.

Geetings


Christian

​