Taking the average of the last 3,6,12 values.

[Cubist]

I have a similar but slightly different than this thread. I want to take the average of the last 3,6,12 cells of each column. For the sake of an example, let's say I need the last 3 (highlighted in the sample).

1) I do not want to include the 0 diagonal or the error cells.
2) When it gets to column M, there are only one values. When this happens, I want to average the one values and {1;1} (a total of 3 values).

The formula provided in the other thread does not exclude the 0 diagonal.

=AVERAGE(TAKE(VSTACK(SEQUENCE(3,,1,0),TOCOL(C1:C12,3)),-3))

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	10/31/2022	3.912291	1.12852	1.014671	1.005627	1.001982	1.000335	1.000915	1	1	1	1	0
2	11/30/2022	4.445134	1.03274	1.003538	1.003536	1.004031	1.001674	1.001016	1	1	1	0	#DIV/0!
3	12/31/2022	3.159267	1.281708	1.015746	1.002671	1.000506	0.999977	1	1	1	0	#DIV/0!	#DIV/0!
4	1/31/2023	4.784979	1.570426	1.017792	1.006631	0.938433	1.00111	1.011781	1.000808	0	#DIV/0!	#DIV/0!	#DIV/0!
5	2/28/2023	7.293705	1.134409	1.007968	1.004732	1.002612	1.000835	0.99895	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
6	3/31/2023	3.677228	1.238119	2.526185	1.002394	1.000627	0.993769	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
7	4/30/2023	4.357553	1.48925	1.012072	1.029621	1.007425	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
8	5/31/2023	3.326643	1.063845	1.027624	1.003144	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
9	6/30/2023	3.487153	1.130711	1.024014	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
10	7/31/2023	5.733536	1.039344	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
11	8/31/2023	2.33832	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
12	9/30/2023	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
Sheet1

 

[Fluff]

How about

=AVERAGE(TAKE(DROP(VSTACK(SEQUENCE(3,,1,0),TOCOL(C1:C12,3)),-4),-3))

 

[Cubist]

How about

=AVERAGE(TAKE(DROP(VSTACK(SEQUENCE(3,,1,0),TOCOL(C1:C12,3)),-4),-3))

Hi Fluff,

I'm not getting the expected result.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		10/31/2022	3.912291	1.12852	1.014671	1.005627	1.001982	1.000335	1.000915	1	1	1	1	0
2		11/30/2022	4.445134	1.03274	1.003538	1.003536	1.004031	1.001674	1.001016	1	1	1	0	#DIV/0!
3		12/31/2022	3.159267	1.281708	1.015746	1.002671	1.000506	0.999977	1	1	1	0	#DIV/0!	#DIV/0!
4		1/31/2023	4.784979	1.570426	1.017792	1.006631	0.938433	1.00111	1.011781	1.000808	0	#DIV/0!	#DIV/0!	#DIV/0!
5		2/28/2023	7.293705	1.134409	1.007968	1.004732	1.002612	1.000835	0.99895	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
6		3/31/2023	3.677228	1.238119	2.526185	1.002394	1.000627	0.993769	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
7		4/30/2023	4.357553	1.48925	1.012072	1.029621	1.007425	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
8		5/31/2023	3.326643	1.063845	1.027624	1.003144	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
9		6/30/2023	3.487153	1.130711	1.024014	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
10		7/31/2023	5.733536	1.039344	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
11		8/31/2023	2.33832	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
12		9/30/2023	0	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
13
14	Fluff's Formula		3.787142	1.287259	1.517315	1.004678
15	Expected		3.853003	1.077966	1.021236	1.01172
16
17	Check		FALSE	FALSE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
C14:F14	C14	=AVERAGE(TAKE(DROP(VSTACK(SEQUENCE(3,,1,0),TOCOL(C1:C12,3)),-4),-3))
C15	C15	=AVERAGE(C9:C11)
D15	D15	=AVERAGE(D8:D10)
E15	E15	=AVERAGE(E7:E9)
F15	F15	=AVERAGE(F6:F8)
C17:F17	C17	=C15=C14

 

[Fluff]

Misread what you wanted, change the -4 to -1

 

[Cubist]

That did it. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.