Taking the averaging of the last 3 values.

[Cubist]

Hi,

I have the following sample data. I'm trying to take the averages of the last 3 values in each column. I'm running into an issue when there are less than 3 values in the column (i.e. columns F & G). What I want when that happens, is to average with 1's. For example in column G, I would want to average (1.003239,1,1) = 1.0018.

Simplified Offical IBNR Model...AH-6.xlsb
	A	B	C	D	E	F	G	H
1		A	B	C	D	E	F	G
2		1.258999	1	1.001535	1	1.000672	1	1.003239
3		0.999623	1.000508	1.001345	1.000205	1.000804	1.001393	#N/A
4		1.000555	1	1	1	1	#N/A	#N/A
5		1.008682	1.000904	1.002106	1	#N/A	#N/A	#N/A
6		1.001063	1.005606	1	#N/A	#N/A	#N/A	#N/A
7		1	1	#N/A	#N/A	#N/A	#N/A	#N/A
8		1	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
9		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
10
11	Average of last 3	1.000354	1.00217	1.000702	1.000068	1.000492	1.000696	1.003239
12
13	Expected	1.000354	1.00217	1.000702	1.000068	1.000492	1.000464	1.00108
Sheet1

Cell Formulas
Range	Formula
B11:H11	B11	=AVERAGE(TAKE(TOCOL(B2:B9,3),-3))
B13:F13	B13	=AVERAGE(TAKE(TOCOL(B2:B9,3),-3))
G13	G13	=AVERAGE(G2:G3,1)
H13	H13	=AVERAGE(1,1,H2)

 

[RoryA]

You could use:

=AVERAGE(TAKE(VSTACK({1;1},TOCOL(B2:B9,3)),-3))

 

[Cubist]

You could use:

=AVERAGE(TAKE(VSTACK({1;1},TOCOL(B2:B9,3)),-3))

That works!

I also want to take the average of the last 6 and 12. Is there a shorter syntax for the 1's array instead of {1;1;1;1;1;1}?

 

[RoryA]

You can use:

=AVERAGE(TAKE(VSTACK(SEQUENCE(3,,1,0),TOCOL(B2:B9,3)),-3))

Just change the first argument to SEQUENCE from 3 to 6 or 12.