Average Last 10 Values in Dynamic Range

[rob51852]

Hello

Can anyone help me with a formula to average the last 10 values in a dynamic range in column AA? I can do the average in a static range but do not know how to change it to work with a dynamic range.

Thank you

 

[63falcondude]

How about something like this?

Excel 2010
	AA	AB	AC
1	Value
2	1		6.5
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
Sheet2

Cell Formulas
Range	Formula
AC2	=AVERAGE(OFFSET(AA1,COUNTA(AA:AA)-10,0,10))

 

[AhoyNC]

This link should help.
https://www.youtube.com/watch?v=zQWNGtNQ8pc

 

[Aladin Akyurek]

With some control:

=AVERAGE(OFFSET(INDEX(AA:AA,MATCH(9.99999999999999E+307,AA:AA)),0,0,-MIN(10,COUNT(AA:AA))))

 

[Ombir]

With greater flexibility to avoid blank cells in the dynamic range:

	AC
2	6.5

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula AC2 {=AVERAGE(INDEX(AA:AA,LARGE(IF(ISNUMBER(AA:AA),ROW(AA:AA)-ROW(AA1)+1),10)):INDEX(AA:AA,MATCH(9.99E+307,AA:AA)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[rob51852]

Thanks guys