Calculate Rolling Average - Skipping Columns

[FalconFlyer]

I am trying to calculate a rolling 12 month average. The hard part is I need to keep a column ("Total" column).

I am not sure the easiest way around this. I'm assuming OFFSET can be used, but am looking for ideas.

TIA
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]May 2017[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Total[/TD]
[TD]Jan 2018[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD]45[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

If you have the AVERAGEIF function:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total	Jan	Feb	Mar	Apr		12 Month Avg.
2	Jul-05									Jul-05
3	1	2	2	3	5	10	12	10	45	1	5	5	2		4.833333333
Sheet54

Cell Formulas
Range	Formula
O3	=AVERAGEIF(A1:M1,"<>"&"Total",A3:M3)

 

[FalconFlyer]

Hi,

If you have the AVERAGEIF function:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total	Jan	Feb	Mar	Apr	12 Month Avg.

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

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Jul-05[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Jul-05[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.833333333[/TD]

</tbody>

Sheet54​

[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: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O3[/TH]
[TD="align: left"]=AVERAGEIF(A1:M1,"<>"&"Total",A3:M3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I apologize for the delay. That is exactly what I was looking for. I did not even think of AVERAGEIF. Thank you!

 

[jtakw]

You're welcome.