Average last seven if not zero

[Tim]

I need to average the last seven numbers (for a week) that aren't zero
in a column. Sometimes there are no zero days during the week,
sometimes one, even up to seven. It changes during the year. Ex.
Monday thru Sunday may all be more than zero, but if Thursday thru
Sunday were zero, I need to include the four previous days in my
average:
A1=10
A2=5
A3=6
A4=0
A5=0
A6=9
A7=10
A8=8
A9=10
A10=15
A11=0
A12=3

In above instance, I need average of A3,A6,A7,A8,A9,A10,A12. (In
column B). I do the average in column B for the whole year, so i need
a running average of the last seven days.

Thank you, Tim

 

[Bernie Deitrick]

In cell B7, array enter the formula (enter using Ctrl-Shift-Enter)

=AVERAGE(IF(OFFSET(A7,-6,0,7,1)>0,OFFSET(A7-6,0,7,1)))

Then copy down as far as you need.

If you need to include negative numbers, change the > to <>

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

I'm sorry - I mis-read your post. The easiest single cell solution would involve a user-defined
function, used like

=Average7(A7)

then copied down.

Paste this code into a standard codemodule:

Function Average7(inCell As Range) As Double
Dim myC As Integer
Dim myR As Long
Average7 = 0
myC = 0
For myR = inCell.Row To 1 Step -1
If Cells(myR, inCell.Column).Value > 0 Then
Average7 = Average7 + Cells(myR, inCell.Column).Value
myC = myC + 1
If myC = 7 Then GoTo found7
End If
Next myR
found7:
Average7 = Average7 / Application.Min(7, myC)
End Function

 

[Tim]

I'm sorry - I mis-read your post.  The easiest single cell solution would involve a user-defined
function, used like

=Average7(A7)

then copied down.

Paste this code into a standard codemodule:

Function Average7(inCell As Range) As Double
Dim myC As Integer
Dim myR As Long
Average7 = 0
myC = 0
For myR = inCell.Row To 1 Step -1
   If Cells(myR, inCell.Column).Value > 0 Then
      Average7 = Average7 + Cells(myR, inCell.Column).Value
      myC = myC + 1
      If myC = 7 Then GoTo found7
   End If
Next myR
found7:
Average7 = Average7 / Application.Min(7, myC)
End Function

--
HTH,
Bernie
MS Excel MVP








- Show quoted text -

Wow, that was fast. I'll give it a try. Thank You!

 

[Mike H]

If you want a formula

=AVERAGE(LOOKUP(LARGE(INDEX(ISNUMBER(A1:A100)*(A1:A100<>0)*ROW(A1:A100),0),{7,6,5,4,3,2,1}),ROW(A1:A100),A1:A100))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

 

[Tim]

I'm sorry - I mis-read your post.  The easiest single cell solution would involve a user-defined
function, used like

=Average7(A7)

then copied down.

Paste this code into a standard codemodule:

Function Average7(inCell As Range) As Double
Dim myC As Integer
Dim myR As Long
Average7 = 0
myC = 0
For myR = inCell.Row To 1 Step -1
   If Cells(myR, inCell.Column).Value > 0 Then
      Average7 = Average7 + Cells(myR, inCell.Column).Value
      myC = myC + 1
      If myC = 7 Then GoTo found7
   End If
Next myR
found7:
Average7 = Average7 / Application.Min(7, myC)
End Function

--
HTH,
Bernie
MS Excel MVP








- Show quoted text -

Thanks Bernie, worked perfect. Thanks for answering Mike. I got
Bernie's UDF to do just what I needed.