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.

A	B	C
Month	Number	Average
1	15
2	19
3	18
4	17
5	16
6	15
7	18
8	19
9	21
10	22

<tbody>
</tbody>

 

[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
1	Month	Number	Average
2	1	15
3	2	19
4	3	18
5	4	17
6	5	16
7	6	15	17
8	7	18	17
9	8	19	16.8
10	9	21	17
11	10	22	17.8
12	11		19
13	12		20

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C7 =AVERAGE(B2:B6) C8 =AVERAGE(B3:B7) C9 =AVERAGE(B4:B8) C10 =AVERAGE(B5:B9) C11 =AVERAGE(B6:B10) C12 =AVERAGE(B7:B11) C13 =AVERAGE(B8:B12) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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
1	Month	Number	Average
2	1	15
3	2	19
4	3	18
5	4	17
6	5	16
7	6	15	17
8	7	18	17
9	8	19	16.8
10	9	21	17
11	10	22	17.8
12	11		19
13	12		20

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C7 =AVERAGE(B2:B6) C8 =AVERAGE(B3:B7) C9 =AVERAGE(B4:B8) C10 =AVERAGE(B5:B9) C11 =AVERAGE(B6:B10) C12 =AVERAGE(B7:B11) C13 =AVERAGE(B8:B12) <tbody> </tbody>

<tbody>
</tbody>

 

[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
1	Month	Number	Average
2	1	15	1
3	3	19	2
4	7	18	5
5	8	17	7.5
6	11	16	8.666667
7	12	15	10.33333

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1 (2)​

Worksheet Formulas Cell Formula C2 =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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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
1	Month	Number	Average		No. of Months=	5
2	1	15
3	2	19
4	3	18
5	4	17
6	5	16
7	6	15	17
8	7	18	16.8
9	8	19	17
10	9	21	17.8
11	10	22	19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1 (3)​

Worksheet Formulas Cell Formula C2 =IF([@Month]<6,"",SUMIFS(Number,Month,"<="&[@Month],Month,">"&[@Month]-$F$1)/COUNTIFS(Month,"<="&[@Month],Month,">"&[@Month]-$F$1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Worksheet Defined Names Name Refers To 'Sheet1 (3)'!Month ='Sheet1 (3)'!$A$2:$A$11 'Sheet1 (3)'!Number ='Sheet1 (3)'!$B$2:$B$11 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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
1	Month	Number	Average		No. of Months=	5
2	1	15
3	2	19
4	3	18
5	4	17
6	5	16
7	6	15	17
8	7	18	16.8
9	8	19	17
10	9	21	17.8
11	10	22	19

<tbody>
</tbody>

Sheet1 (3)​

Worksheet Formulas Cell Formula C2 =IF([@Month]<6,"",SUMIFS(Number,Month,"<="&[@Month],Month,">"&[@Month]-$F$1)/COUNTIFS(Month,"<="&[@Month],Month,">"&[@Month]-$F$1)) <tbody> </tbody>

<tbody>
</tbody>

Worksheet Defined Names Name Refers To 'Sheet1 (3)'!Month ='Sheet1 (3)'!$A$2:$A$11 'Sheet1 (3)'!Number ='Sheet1 (3)'!$B$2:$B$11 <tbody> </tbody>

<tbody>
</tbody>

=IF([@Month]<6,

<tbody>
</tbody>

 

[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.

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

<tbody>
</tbody>

 

[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))