Average last seven if not zero
 

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

Average last seven if not zero
 

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


"Tim" wrote in message
...
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

Average last seven if not zero
 

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Tim" wrote in message
...
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

Average last seven if not zero
 

On Nov 13, 11:19*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in . ..



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

"Tim" wrote in message
....
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- Hide quoted text -

- Show quoted text -

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

Average last seven if not zero
 

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:A 100),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" wrote:

On Nov 13, 11:19 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in . ..



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

"Tim" wrote in message
....
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- Hide quoted text -

- Show quoted text -

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

Average last seven if not zero
 

On Nov 13, 11:19*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in . ..



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

"Tim" wrote in message
....
I need toaveragethe 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 needaverageof A3,A6,A7,A8,A9,A10,A12. (In
column B). I do theaveragein column B for the whole year, so i need
a runningaverageof the last seven days.

Thank you, Tim- Hide quoted text -

- Show quoted text -

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