OFFSET and MATCH? How to get rolling 3 month average of income based on moving anchor?

[FKalinx]

Hello Mr Excel

I am trying to get a rolling average of income for three months. I have created the anchor for current month (which automatically moves based on current month), let's say in cell D1 and want to then average the income in cell B3, C3 and D3 - see below. If the current month was Feb, the anchor would be E1 and the cells I would need averaging would be C3, D3 and E3.

Month - Nov Dec Jan (Current Month)
Cost - 100 200 300
Income 400 400 500

I have tried using OFFSET and MATCH but can't get it to work.

Can a genius please help me out?

Thanks.

 

[jorismoerings]

Hi,

Check if this helps:

Book1
	A	B	C	D	E	F
1	Month	okt	nov	dec	jan	feb
2	Cost	175	100	200	300
3	Income	350	400	400	500
4	Average Income	0	375	383	433	450
Sheet1

Cell Formulas
Range	Formula
B4	{=IFERROR(AVERAGE(OFFSET($A$1,2,MATCH(MONTH(B$1),MONTH(1:1)+0,0)-1,1,-3)),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[FKalinx]

Hi,

Check if this helps:

	A	B	C	D	E	F
Month
Cost
Income
Average Income

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

[TD="align: right"]okt[/TD]
[TD="align: right"]nov[/TD]
[TD="align: right"]dec[/TD]
[TD="align: right"]jan[/TD]
[TD="align: right"]feb[/TD]

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

[TD="align: right"]175[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]350[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]0[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]383[/TD]
[TD="align: right"]433[/TD]
[TD="align: right"]450[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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] "]B4[/TH]
[TD="align: left"]{=IFERROR(AVERAGE(OFFSET($A$1,2,MATCH(MONTH(B$1),MONTH(1:1)+0,0)-1,1,-3)),0)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thanks, you're a star!