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

May 2017	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total	Jan 2018	Feb	Mar	Apr
1	2	2	3	5	10	12	10	45	1	5	5	2

<tbody>
</tbody>

 

[jtakw]

Hi,

If you have the AVERAGEIF function:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">May</td><td style=";">Jun</td><td style=";">Jul</td><td style=";">Aug</td><td style=";">Sep</td><td style=";">Oct</td><td style=";">Nov</td><td style=";">Dec</td><td style=";">Total</td><td style=";">Jan</td><td style=";">Feb</td><td style=";">Mar</td><td style=";">Apr</td><td style="text-align: right;;"></td><td style=";">12 Month Avg.</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">Jul-05</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">Jul-05</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10</td><td style="text-align: right;;">12</td><td style="text-align: right;;">10</td><td style="text-align: right;;">45</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">4.833333333</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet54</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O3</th><td style="text-align:left">=AVERAGEIF(<font color="Blue">A1:M1,"<>"&"Total",A3:M3</font>)</td></tr></tbody></table></td></tr></table><br />

 

[FalconFlyer]

Hi,

If you have the AVERAGEIF function:

	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

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

Sheet54​

Worksheet Formulas Cell Formula O3 =AVERAGEIF(A1:M1,"<>"&"Total",A3:M3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.