# Average Last 10 Values in Dynamic Range

#### rob51852

##### Board Regular
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

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Excel 2010
AAABAC
1Value
216.5
32
43
54
65
76
87
98
109
1110
1211
Sheet2
Cell Formulas
RangeFormula
AC2=AVERAGE(OFFSET(AA1,COUNTA(AA:AA)-10,0,10))

With some control:

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

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

AC
26.5

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
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>

Last edited:
Thanks guys

Replies
20
Views
398
Replies
2
Views
96
Replies
2
Views
563
Replies
5
Views
295
Replies
3
Views
176

1,196,078
Messages
6,013,292
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back