Find Average for n 3 month across columns

[Liz_I3]

Hello I want the average of the current last 3 month on my spreadsheet. .Below is a copy of what my spreadsheet looks like it show the average for Dec, Jan & Feb (which I just keyed in) Next month I would want the average for Jan, Feb & Mar. etc
I found this formula but it just does not do it. Average(Offset( C2,0,Count(C2:P2)-3,1,3)).

11/01/20​

12/01/20​

01/01/21​

02/01/21​

03/01/21​

04/01/21​

05/01/21​

06/01/21​

07/01/21​

08/01/21​

09/01/21​

10/01/21​

11/01/21​

12/01/21​

Average

10​

20​

30​

40​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

30​

Thanks for taking the time to help

L

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	11/01/20	12/01/20	01/01/21	02/01/21	03/01/21	04/01/21	05/01/21	06/01/21	07/01/21	08/01/21	09/01/21	10/01/21	11/01/21	12/01/21	Average
2	10	20	30	40	0	0	0	0	0	0	0	0	0	0	25
Sheet2

Cell Formulas
Range	Formula
O2	O2	=AVERAGE(A2:INDEX(A2:N2,LOOKUP(2,1/(A2:N2<>0),COLUMN(A2:N2))))

This finds the last non-zero number, and averages everything before that, inclusive.

 

[lrobbo314]

I think the OP wanted the last 3 months, not everything before the last 0.

Depending on your version of Excel, here are some options.

FactSheet_Data_New (8).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	11/1/2020	12/1/2020	1/1/2021	2/1/2021	3/1/2021	4/1/2021	5/1/2021	6/1/2021	7/1/2021	8/1/2021	9/1/2021	10/1/2021	11/1/2021	12/1/2021	Average	Average	Average
2	10	20	30	40	50	0	0	0	0	0	0	0	0	0	40	40	40
Sheet1

Cell Formulas
Range	Formula
O2	O2	=AVERAGE(INDEX(A2:N2,,ROW(INDIRECT(MATCH(EOMONTH(TODAY(),-1)+1,A1:N1,0)-2 &":" & MATCH(EOMONTH(TODAY(),-1)+1,A1:N1,0)))))
P2	P2	=AVERAGE(INDEX(A2:N2,,SEQUENCE(,3,MATCH(EOMONTH(TODAY(),-1)+1,A1:N1,0)-2)))
Q2	Q2	=AVERAGE(IF(ISNUMBER(MATCH(A1:N1,EOMONTH(TODAY(),{-1,-2,-3})+1,0)),A2:N2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Eric W]

I think the OP wanted the last 3 months, not everything before the last 0.

D'oh! ? Of course, I got off course somewhere.

Your formulas look like they are work by matching on the date, which should be fine. But just for informational purposes, here's where the original formula went awry:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	11/01/20	12/01/20	01/01/21	02/01/21	03/01/21	04/01/21	05/01/21	06/01/21	07/01/21	08/01/21	09/01/21	10/01/21	11/01/21	12/01/21	Average
2	10	20	30	40											30
Sheet2

Cell Formulas
Range	Formula
O2	O2	=AVERAGE(OFFSET(C2,0,COUNT(C2:N2)-3,1,3))

It works fine IF the cells after the last month are empty, not filled with zeros. If they do have zeros, a hybrid of the OP's original formula and my formula would work without using the date.

 

[Liz_I3]

I think the OP wanted the last 3 months, not everything before the last 0.

Depending on your version of Excel, here are some options.

FactSheet_Data_New (8).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	11/1/2020	12/1/2020	1/1/2021	2/1/2021	3/1/2021	4/1/2021	5/1/2021	6/1/2021	7/1/2021	8/1/2021	9/1/2021	10/1/2021	11/1/2021	12/1/2021	Average	Average	Average
2	10	20	30	40	50	0	0	0	0	0	0	0	0	0	40	40	40
Sheet1

Cell Formulas
Range	Formula
O2	O2	=AVERAGE(INDEX(A2:N2,,ROW(INDIRECT(MATCH(EOMONTH(TODAY(),-1)+1,A1:N1,0)-2 &":" & MATCH(EOMONTH(TODAY(),-1)+1,A1:N1,0)))))
P2	P2	=AVERAGE(INDEX(A2:N2,,SEQUENCE(,3,MATCH(EOMONTH(TODAY(),-1)+1,A1:N1,0)-2)))
Q2	Q2	=AVERAGE(IF(ISNUMBER(MATCH(A1:N1,EOMONTH(TODAY(),{-1,-2,-3})+1,0)),A2:N2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

HI Irobbo314
I used your first suggestion =AVERAGE(IF(ISNUMBER(MATCH(A1:N1,EOMONTH(TODAY(),{-1,-2,-3})+1,0)),A2:N2)) and it is working well

My version of excel 2016 did not recognize ,SEQUENCE I got a # NAME? error.

Thank you very much for your time.

D'oh! ? Of course, I got off course somewhere.

Your formulas look like they are work by matching on the date, which should be fine. But just for informational purposes, here's where the original formula went awry:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	11/01/20	12/01/20	01/01/21	02/01/21	03/01/21	04/01/21	05/01/21	06/01/21	07/01/21	08/01/21	09/01/21	10/01/21	11/01/21	12/01/21	Average
2	10	20	30	40											30
Sheet2

Cell Formulas
Range	Formula
O2	O2	=AVERAGE(OFFSET(C2,0,COUNT(C2:N2)-3,1,3))

It works fine IF the cells after the last month are empty, not filled with zeros. If they do have zeros, a hybrid of the OP's original formula and my formula would work without using the date.

Hi Eric
Thank you for looking at this for me. I thought my formula would have worked also but I get a result for only the last 3 months not the 3 months that I wanted Dec, Jan & Feb