## Calculate average of last 10 data (possible blanks) in excel

### 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:

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- 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.

### 17 Responses to “Calculate average of last 10 data (possible blanks) in excel”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Oscar, what if list of data and blank cells are in a row.

Let's say data starts from Column C.

Cyril,

Array formula:

Yes, this CSE would average the last 10 Columns. But it would include the blanks.

Cyril,

Yes, the formula includes the ten last values. But the blanks are not counted and calculated?

Given the example posted above that is {1;"" ; 2;"" ; 2; 3; 4; 5;"" ;"" ;"" ; 6; 7;"" ; 8; 9;"" ; 10;"" ;"" ; 11; 12; 13;"" ; 14}, the average of the last 10 values (integers) would be 9.5.

Given that the data are in Row 1 from Column I to AG.

Given the formula in D1 as follows =AVERAGE(INDEX(I1:AG1, LARGE(IF($I$1:$AG$1"", COLUMN($I$1:$AG$1), ""), 10)):INDEX($I1:$AG1,MATCH(1E+307, $I$1:$AG$1))) CSE.

The formula would return 11.5 that is the average of the last 10 Columns. {9;"" ; 10;"" ;"" ; 11; 12; 13;"" ; 14}

The blanks are not counted, they are not calculated but the formula doesn't return the average of the last 10 values.

Would you agree?

Cyril,

COLUMN($I$1:$AG$1) returns {9, 10, .. and so on}

COLUMN(1:1) returns {1, 2, ... and so on}

MATCH(COLUMN($I$1:$AG$1),COLUMN($I$1:$AG$1)) returns the right array and size for your range.

Try this:

Very nice.

Consider this as well:

=AVERAGE(IF(COLUMN(I1:AG1)>=LARGE(IF(ISNUMBER(I1:AG1), COLUMN(I1:AG1)),MIN(10,COUNT(I1:AG1))), IF(ISNUMBER(I1:AG1),I1:AG1)))

Thanks Oscar.

Cyril,

thanks for your contribution!

I am trying to use this same formula, but for sum of last 10, instead of average. I replace AVERAGE in the formula, then I get an error. What do I need to correct make this happen with last 10 entries.

Thank you.

Hi Bill, are your data arranged in a Column or in a Row?

if in a row:

=SUM(IF(COLUMN(I6:AG6)>=LARGE(IF(ISNUMBER(I6:AG6), COLUMN(I6:AG6)),MIN(10,COUNT(I6:AG6))), IF(ISNUMBER(I6:AG6),I6:AG6))) Ctrl + Shift + Enter, not just enter.

If In column, I have a solution with Named ranges. But for sure Oscar will have a better solution.

Cheers.

Bill, if in a column, wouldn't =SUM(INDEX(A:A,LARGE(IF($A$1:$A$12500"",ROW($A$1:$A$12500),""),10)):INDEX(A:A,MATCH(9.99999999999999E+307,$A$1:$A$12500))) CSE work for you?

Hi,

I have a large data set of 546825 rows x 14 column. I need to average each 5 rows of this data; 1-5, 5-10, etc. Can you show me how do it in excel? Thanks indeed.

Cheers,

Andy

Hi Oscar

Nice to help us.

I would like you to help me with a function that find average of the first 3 scores. As your example there is blank cells.

I don't know what to change in the function you already published.

=AVERAGE(INDEX(A:A;LARGE(IF($A$1:$A$25"";ROW($A$1:$A$25);"");10)):INDEX(A:A;MATCH(1E+307;$A$1:$A$25)))

thank you

Theodor

tried to adapt this formula for "calculate average of last 10 (ignore blanks)in excel" for my data that is in columns F-AE but something is wrong:

=AVERAGE(OFFSET(INDEX(2:2,,COUNT($F2:$AE2)),,-9,1,10))

I really want to average the last 20. What am I doing wrong?

janis,

The array formula in this post works only for values in a single column.

I struggled a bit with getting the formula to work before I realized that the comma in 9,9E+307 needs to be changed for a dot for my regional settings. If anyone else has the same problem use this code:

=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)))

I am trying to average the last five numbers of data arrayed in a row. How does the formula have to be modified for that?