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

F

Fargol

New Member
Joined
Nov 15, 2017
Messages
2
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
"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?
 
Upvote 0
mikerickson said:
"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?
Click to expand...

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
[TABLE="width: 565"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]3/7/17[/TD]
[TD]192.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]118[/TD]
[TD]106[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/8/17[/TD]
[TD]191.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]125[/TD]
[TD]94[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/9/17[/TD]
[TD]189.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]124[/TD]
[TD]95[/TD]
[TD]96[/TD]
[/TR]
[TR]
[TD]3/10/17[/TD]
[TD]189.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]120[/TD]
[TD]118[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/11/17[/TD]
[TD]190.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]126[/TD]
[TD]118[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/12/17[/TD]
[TD]191.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD] [/TD]
[TD]90[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]3/13/17[/TD]
[TD]189.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]134[/TD]
[TD] [/TD]
[TD]114[/TD]
[/TR]
[TR]
[TD]3/14/17[/TD]
[TD]190.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]116[/TD]
[TD]88[/TD]
[TD]116[/TD]
[/TR]
[TR]
[TD]3/15/17[/TD]
[TD]190.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]116[/TD]
[TD]86[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3/16/17[/TD]
[TD]190.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]110[/TD]
[TD]88[/TD]
[TD]108[/TD]
[/TR]
[TR]
[TD]3/17/17[/TD]
[TD]191.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD] [/TD]
[TD]112[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks.


mikerickson said:
"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?
Click to expand...

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
[TABLE="width: 565"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]3/7/17[/TD]
[TD]192.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]118[/TD]
[TD]106[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/8/17[/TD]
[TD]191.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]125[/TD]
[TD]94[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/9/17[/TD]
[TD]189.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]124[/TD]
[TD]95[/TD]
[TD]96[/TD]
[/TR]
[TR]
[TD]3/10/17[/TD]
[TD]189.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]120[/TD]
[TD]118[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/11/17[/TD]
[TD]190.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]126[/TD]
[TD]118[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3/12/17[/TD]
[TD]191.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD] [/TD]
[TD]90[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]3/13/17[/TD]
[TD]189.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]134[/TD]
[TD] [/TD]
[TD]114[/TD]
[/TR]
[TR]
[TD]3/14/17[/TD]
[TD]190.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]116[/TD]
[TD]88[/TD]
[TD]116[/TD]
[/TR]
[TR]
[TD]3/15/17[/TD]
[TD]190.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]116[/TD]
[TD]86[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3/16/17[/TD]
[TD]190.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD]110[/TD]
[TD]88[/TD]
[TD]108[/TD]
[/TR]
[TR]
[TD]3/17/17[/TD]
[TD]191.0[/TD]
[TD]8:00 AM[/TD]
[TD]8:00 AM[/TD]
[TD] [/TD]
[TD]112[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks.
 
Upvote 0
Hello,

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

Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Average of last three non-zero, but one i a column
Replies
7
Views
219
Fluff
Fluff
T
  • Solved
Index and Match (?) for the last non-zero value in a row
Replies
3
Views
683
Fluff
Fluff
T
  • Question Question
Moving average last 5 numeric number, N/A for any zero cells and the omitting that for next moving average
Replies
3
Views
327
Cubist
Cubist
T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
71
Peter_SSs
Peter_SSs
N
  • Question Question
Column with Min/Average values at the end of Pivot Table
Replies
0
Views
130
Nurces
N

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top