Calculate running average of last 10 data with random blank cells
- List of data and blank cells in a column which will be added from day to day.
- There are sometimes blank cells in column.
How to get the average of the 10 most recent data? The average will change from day to day.
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 C3:
How to create an array formula
- Select cell C3
- Copy (Ctrl + c) array formula
- Paste (Ctrl + v) array formula to formula bar.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- Release all keys.
How this array formula works in cell C18
The array formula calculates a cell range to use and then averages the numbers in that cell range.
Step 1 - Extract row numbers of not empty cells
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
The logical expression contains an expanding cell range that grows when the cell is copied to cells below.
IF($B$3:B18<>"", ROW($B$3:B18), "")
becomes
IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}, ROW($B$3:B18), "")
becomes
IF({TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE}, {3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18}, "")
and returns {3;"";5;"";7;8;9;10;"";"";"";14;15;"";17;18}.
Step 2 - Calculate the 10th largest row number
The LARGE function returns the k-th largest number. LARGE( array, k)
LARGE(IF($B$3:B18<>"", ROW($B$3:B18), ""), 10)
becomes
LARGE({3;"";5;"";7;8;9;10;"";"";"";14;15;"";17;18}, 10)
and returns 3.
Step 3 - Use row number to create a cell reference
The INDEX function lets you build a cell reference based on a row and column number.
INDEX(B:B, LARGE(IF($B$3:B3<>"", ROW($B$3:B3), ""), 10))
becomes
INDEX(B:B, 3)
and returns B3.
Step 4 - Build complete cell reference
A cell reference may contain two parts separated by a colon.
INDEX(B:B, LARGE(IF($B$3:B3<>"", ROW($B$3:B3), ""), 10)):B18
returns B3:B18.
Step 5 - Average values
The AVERAGE function calculates the average based on the cell reference we calculated earlier.
AVERAGE(INDEX(B:B, LARGE(IF($B$3:B3<>"", ROW($B$3:B3), ""), 10)):B18)
becomes
AVERAGE(B3:B18)
and returns 53.8
Step 6 - Return - if calculation returns error
The IFERROR fucntion returns "-" if the formula returns an error which it does until 10 numbers are found in the cell range.
More than 1300 Excel formulas
Excel categories
22 Responses to “Calculate running average of last 10 data with random blank cells”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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?
hi, good formular, it did though take me a while to make it work, as I had never worked with array functions before.. I didnt event know how to insert them in excel ! Good stuff
Thank you, Henrik.
I am trying to calculate the running average of the largest 10 out of last 20 values in a ROW (exclude blanks). I have been trying to adapt this formula but not having a lot of luck. Can anyone lead me in the right direction?
=AVERAGE(INDEX(I1:AG1, LARGE(IF($I$1:$AG$1"", MATCH(COLUMN($I$1:$AG$1),COLUMN($I$1:$AG$1)), ""), 10)):INDEX($I1:$AG1,MATCH(1E+307, $I$1:$AG$1)))
Glenn,
The LARGE function excludes blanks automatically, what about this formula?
=AVERAGE(LARGE(I1:AG1,ROW($A$1:$A$10)))
Oscar,
Thanks for the reply!
After rereading my post, I noticed I worded my question wrong I will also use an example from my working spreadshe. This formula looks at the entire ROW of data from C3 to PG3 and averages the last 10 values excluding blanks:
AVERAGE(INDEX(C3:PG3, LARGE(IF($C3:$PG3"", MATCH(COLUMN($C3:$PG3),COLUMN($C3:$PG3)), ""), 10)):INDEX($C3:$PG3,MATCH(1E+307, $C3:$PG3))))
I need the formula to look at the last 20 values, and AVERAGE the SMALLEST 10 values out of those 20. I mistakenly said largest in my original question.
I'm looking at your formula and I don't see where it is looking at the last 20 values.
Thank you again for your help!!