### Question:

This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges \$A\$1:\$A\$25 in formula below.

### Array formula in cell F18:

=AVERAGE(INDEX(A:A, LARGE(IF(\$A\$1:\$A\$25<>"", ROW(\$A\$1:\$A\$25), ""), 10)):INDEX(A:A, MATCH(9,9E+307, \$A\$1:\$A\$25)))

### How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

### How this array formula works

=AVERAGE(INDEX(A:A, LARGE(IF(\$A\$1:\$A\$25<>"", ROW(\$A\$1:\$A\$25), ""), 10)):INDEX(A:A, MATCH(9,9E+307, \$A\$1:\$A\$25)))

Step 1 - Calculate cell reference of the 10th last value

=AVERAGE(INDEX(A:A, LARGE(IF(\$A\$1:\$A\$25<>"", ROW(\$A\$1:\$A\$25), ""), 10)):INDEX(A:A, MATCH(9,9E+307, \$A\$1:\$A\$25)))

INDEX(A:A, LARGE(IF(\$A\$1:\$A\$25<>"", ROW(\$A\$1:\$A\$25), ""), 10))

becomes

INDEX(A:A, LARGE(IF({1;"" ; 2;"" ; 2; 3; 4; 5;"" ;"" ;"" ; 6; 7;"" ; 8; 9;"" ; 1;"" ;"" ; 11; 12; 13;"" ; 14}<>"", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25}, ""), 10))

becomes

INDEX(A:A, LARGE(IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25}, ""), 10))

becomes

INDEX(A:A, LARGE({1; ""; 3; ""; 5; 6; 7; 8; ""; ""; ""; 12; 13; ""; 15; 16; ""; 18; ""; ""; 21; 22; 23; ""; 25}, 10))

becomes

INDEX(A:A, 8 )

and returns cell reference \$A\$8

Step 2 - Calculate cell reference of the last value

=AVERAGE(INDEX(A:A, LARGE(IF(\$A\$1:\$A\$25<>"", ROW(\$A\$1:\$A\$25), ""), 10)):INDEX(A:A, MATCH(9,9E+307, \$A\$1:\$A\$25)))

INDEX(A:A, MATCH(9,9E+307, \$A\$1:\$A\$25))

becomes

INDEX(A:A, MATCH(9,9E+307, {1;"" ; 2;"" ; 2; 3; 4; 5;"" ;"" ;"" ; 6; 7;"" ; 8; 9;"" ; 1;"" ;"" ; 11; 12; 13;"" ; 14})

becomes

INDEX(A:A, 25)

and returns cell reference \$A\$25.

Step 3 - Calculate average

=AVERAGE(INDEX(A:A, LARGE(IF(\$A\$1:\$A\$25<>"", ROW(\$A\$1:\$A\$25), ""), 10)):INDEX(A:A, MATCH(9,9E+307, \$A\$1:\$A\$25)))

becomes

=AVERAGE(\$A\$8:\$A\$25)

becomes

=AVERAGE({5; ""; ""; ""; 6; 7; ""; 8; 9; ""; 1""; ""; ""; 11; 12; 13; ""; 14})

and returns 9,5.

last_10_data_average.xls
(Excel 97-2003 Workbook *.xls)

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the rownumber of a reference

LARGE(array,k) returns the k-th largest row number in this data set.

AVERAGE(number1, number2, ... ) returns the average (arithmetic mean) of its arguments which can be numbers or names, arrays or references that contain numbers.