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]

 

[Toadstool]

So you can't average month 5 because there aren't 5 previous months?

Just put in with relative addresses and it will change as you require.

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Average[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]17[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]17[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]16.8[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]17[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]17.8[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]19[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=AVERAGE(B2:B6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=AVERAGE(B3:B7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"]=AVERAGE(B4:B8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C10[/TH]
[TD="align: left"]=AVERAGE(B5:B9)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C11[/TH]
[TD="align: left"]=AVERAGE(B6:B10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C12[/TH]
[TD="align: left"]=AVERAGE(B7:B11)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C13[/TH]
[TD="align: left"]=AVERAGE(B8:B12)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gesyca_is_joy]

Well, yes I would bracket the final formula with an IFERROR so that it returned a blank cell if there weren't enough months to average. but the reason I didn't do the formula you wrote below is because, for the example, i am averaging 5 months. but that is also dynamic. In my actual workbook the table shown below starts at A5 and I have a value in B3 which is currently "5" and my average formula is supposed to look at this cell to figure out how many months back to calculate. that number also changes based on what I am trying to forecast.

So you can't average month 5 because there aren't 5 previous months?

Just put in with relative addresses and it will change as you require.

	A	B	C

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Average[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]17[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]17[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]16.8[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]17[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]17.8[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]19[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C7[/TH]
[TD="align: left"]=AVERAGE(B2:B6)[/TD]
[/TR]
[TR]
[TH]C8[/TH]
[TD="align: left"]=AVERAGE(B3:B7)[/TD]
[/TR]
[TR]
[TH]C9[/TH]
[TD="align: left"]=AVERAGE(B4:B8)[/TD]
[/TR]
[TR]
[TH]C10[/TH]
[TD="align: left"]=AVERAGE(B5:B9)[/TD]
[/TR]
[TR]
[TH]C11[/TH]
[TD="align: left"]=AVERAGE(B6:B10)[/TD]
[/TR]
[TR]
[TH]C12[/TH]
[TD="align: left"]=AVERAGE(B7:B11)[/TD]
[/TR]
[TR]
[TH]C13[/TH]
[TD="align: left"]=AVERAGE(B8:B12)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Toadstool]

So your example data was not a representation? Is this example below what you want?

It may be easier if you gave examples of actual data with your expected results.

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Average[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]7.5[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]8.666667[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]10.33333[/TD]

</tbody>

Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2
[/TH]
[TD="align: left"]=SUMIFS($A$2:$A$7,$A$2:$A$7,"<="&A2,$A$2:$A$7,">"&A2-5)/COUNTIFS($A$2:$A$7,"<="&A2,$A$2:$A$7,">"&A2-5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gesyca_is_joy]

Apologies, I’m not sure how to put a named table into this forum.

In essence, I have a named table, that i put in the month, my actual demand figures and a third column where I want to be able to calculate a dynamic average.

The columns look exactly as above (units in thousands), and i have a cell off to one side of the named table range that will hold the number of months I am trying to average, this value will change, which is why i want the formula to reference that cell instead of a hard value.

the challenge is that all the formulas i know how to write can’t come up with the combination i’m looking for without me having to manually change the formula if my base criterion (number of rows to average) changes.

but since it’s a named table, i could write the formula to reference the column names “Month”, “Demand” “Average” are the names of the columns in the table. I could reference the column names rather than the cells (B7:B15) in the formula

 

[Toadstool]

I'm still not sure I understand as your example never gave an expected result. Is this what you want?

	A	B	C	D	E	F
No. of Months=

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Average[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]16.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]17.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1 (3)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2
[/TH]
[TD="align: left"]=IF([@Month]<6,"",SUMIFS(Number,Month,"<="&[@Month],Month,">"&[@Month]-$F$1)/COUNTIFS(Month,"<="&[@Month],Month,">"&[@Month]-$F$1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]'Sheet1 (3)'!Month[/TH]
[TD="align: left"]='Sheet1 (3)'!$A$2:$A$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]'Sheet1 (3)'!Number[/TH]
[TD="align: left"]='Sheet1 (3)'!$B$2:$B$11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gesyca_is_joy]

What you have below is almost exactly what I am looking for. the only difference is that this part of your formula "=IF([@Month]<6," is static not dynamic, so if the value in F1 changes to, say 3 (for quarterly average) then the formula would be off? I was trying to basically write the formula and wrap it in an IFERROR so that if there are not enough rows above to average and the formula ends up in an error the IFERROR would tell the cell to stay blank.

I'm still not sure I understand as your example never gave an expected result. Is this what you want?

	A	B	C	D	E	F
No. of Months=

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Average[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]16.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]17.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1 (3)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=IF([@Month]<6,"",SUMIFS(Number,Month,"<="&[@Month],Month,">"&[@Month]-$F$1)/COUNTIFS(Month,"<="&[@Month],Month,">"&[@Month]-$F$1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Defined Names[TABLE="width: 100%"]
<tbody>[TR]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH]'Sheet1 (3)'!Month[/TH]
[TD="align: left"]='Sheet1 (3)'!$A$2:$A$11[/TD]
[/TR]
[TR]
[TH]'Sheet1 (3)'!Number[/TH]
[TD="align: left"]='Sheet1 (3)'!$B$2:$B$11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1055"]
<tbody>[TR]
[TD="align: left"]=IF([@Month]<6,[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gesyca_is_joy]

Ok, in trying the formula I get errors. let me start fresh and see if I can do better at articulating what I am trying to achieve.

The table below has the following information

• column A is Time Period (usually a month,denoted as a number for simplicity now.)
• Column B is Actuals (in currency for this example)
• Column C is Rolling Average.
• Columns E and F are the name and number for the criterion to average.
  • the RA Value can change, but it will always represent the number of periods i want to include in my rolling average.

GOAL: I want to write a formula that will calculate the average Actuals for the previous periods, the number of which is defined in cell F1 in the table below. the total should then appear in the column C and as the formula moves down a row, the cells it averages should also move with it, however, it should ONLY ever average the number of rows above as are defined in cell F1.

Because I am using a named table that will take my formula and apply it to the entire column, the top couple rows, or any rows added on to the end that have no Actuals in them yet will always result in an error, so i want to wrap the aforementioned formula with an IFERROR so that all errors result in a blank cell.

I am sorry that I articulated this so poorly in the beginning. I tried to clarify; this problem is making me pound my head into the desk.


[TABLE="width: 100"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Period[/TD]
[TD]Actuals[/TD]
[TD]Rolling Average[/TD]
[TD][/TD]
[TD]RA Value=[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]01[/TD]
[TD]10571[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02[/TD]
[TD]16579[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]03[/TD]
[TD]14598[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]04[/TD]
[TD]10326[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]05[/TD]
[TD]12935[/TD]
[TD]13019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]06[/TD]
[TD]14755[/TD]
[TD]13610[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]07[/TD]
[TD]16429[/TD]
[TD]13154[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]08[/TD]
[TD]10588[/TD]
[TD]13611[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]09[/TD]
[TD]11932[/TD]
[TD]13677[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]10[/TD]
[TD]9598[/TD]
[TD]13426[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]11[/TD]
[TD]19756[/TD]
[TD]12137[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]12[/TD]
[TD]12358[/TD]
[TD]12969[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Besserwisser]

Hi,

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


CTRL+SHIFT+Enter




Greetings



Christian

 

[jasonb75]

Have you tried

=IF((ROW([@Demand])-ROW([[#Headers][@Demand]))<$B$3,"",AVERAGE(OFFSET([@Demand],,-$B$3))