Question:

Answer:

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.

Download excel sample file for this tutorial.

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

Functions in this article:

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.