Need to get average of last 10 non-zero values in a row (grows daily)

[Fargol]

I have a spreadsheet with hundreds of rows that grows daily. I'd like to set up a cell at the top that'll display the average of the last 10
non-zero, non-blank rows and adjust each time a new value is added. I've tried many things, all to no avail.

Thanks in advance.

 

[mikerickson]

"non-zero, non-blank rows" sound like you have a complicated structure.

Are there blank rows between non-blank rows?
What is a "zero row"? A row with one zero somewhere in the row or a row where all the cells contain 0?
How many columns are in your data set? If it varies, what is the maximum?

 

[Fargol]

"non-zero, non-blank rows" sound like you have a complicated structure.

Are there blank rows between non-blank rows?
What is a "zero row"? A row with one zero somewhere in the row or a row where all the cells contain 0?
How many columns are in your data set? If it varies, what is the maximum?

Yes. some rows might be blank if I neglected to put anything in it that day. Non-zero doesn't really apply since it'll either have a number > 0, or be blank.

Let me also clarify - I currently have several hundred rows. Theoretically, there is no maximum. In each row is various data such as date and time, and three cells (consecutive columns) in each row contain the values I wish to average over the last 10 days.

It looks like this ... Columns E, F, and G are the ones I want to display the last 10 day average for. As you can see, there are blanks. So, to simplify it, three cells at the top of the sheet will
display the average for the last 3 values in E, F, and G. So E's average would be (116+110)/2, F's would be (86+88+112) / 3, and G's would be (100+108)/2.

A B C D E F G

3/7/17	192.0	8:00 AM	8:00 AM	118	106
3/8/17	191.0	8:00 AM	8:00 AM	125	94
3/9/17	189.0	8:00 AM	8:00 AM	124	95	96
3/10/17	189.0	8:00 AM	8:00 AM	120	118
3/11/17	190.0	8:00 AM	8:00 AM	126	118
3/12/17	191.0	8:00 AM	8:00 AM		90	106
3/13/17	189.0	8:00 AM	8:00 AM	134		114
3/14/17	190.0	8:00 AM	8:00 AM	116	88	116
3/15/17	190.0	8:00 AM	8:00 AM	116	86	100
3/16/17	190.0	8:00 AM	8:00 AM	110	88	108
3/17/17	191.0	8:00 AM	8:00 AM		112

<colgroup><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

I hope I made that clear. I'm starting to wonder if it's even possible to do what I want

Thanks.

"non-zero, non-blank rows" sound like you have a complicated structure.

Are there blank rows between non-blank rows?
What is a "zero row"? A row with one zero somewhere in the row or a row where all the cells contain 0?
How many columns are in your data set? If it varies, what is the maximum?

Yes. some rows might be blank if I neglected to put anything in it that day. Non-zero doesn't really apply since it'll either have a number > 0, or be blank.

Let me also clarify - I currently have several hundred rows. Theoretically, there is no maximum. In each row is various data such as date and time, and three cells (consecutive columns) in each row contain the values I wish to average over the last 10 days.

It looks like this ... Columns E, F, and G are the ones I want to display the last 10 day average for. As you can see, there are blanks. So, to simplify it, three cells at the top of the sheet will
display the average for the last 3 values in E, F, and G. So E's average would be (116+110)/2, F's would be (86+88+112) / 3, and G's would be (100+108)/2.

A B C D E F G

3/7/17	192.0	8:00 AM	8:00 AM	118	106
3/8/17	191.0	8:00 AM	8:00 AM	125	94
3/9/17	189.0	8:00 AM	8:00 AM	124	95	96
3/10/17	189.0	8:00 AM	8:00 AM	120	118
3/11/17	190.0	8:00 AM	8:00 AM	126	118
3/12/17	191.0	8:00 AM	8:00 AM		90	106
3/13/17	189.0	8:00 AM	8:00 AM	134		114
3/14/17	190.0	8:00 AM	8:00 AM	116	88	116
3/15/17	190.0	8:00 AM	8:00 AM	116	86	100
3/16/17	190.0	8:00 AM	8:00 AM	110	88	108
3/17/17	191.0	8:00 AM	8:00 AM		112

<colgroup><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

I hope I made that clear. I'm starting to wonder if it's even possible to do what I want

Thanks.

 

[Fennek]

Hello,

try this function, in A1 type =last10(A2)

Function Last10(rng As Range) As Double
Application.EnableEvents = False
col = rng.Column
lr = Cells(Rows.Count, col).End(xlUp).Row
For i = lr To 2 Step -1
If Not IsEmpty(Cells(i, col)) Then r = r + 1
    s = s + Cells(i, col)
If r = 10 Then Exit For
Next i
Last10 = s
Application.EnableEvents = True
End Function

regards