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" 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 messagenews:(E-Mail Removed)...
> >
> >
> >
> > > 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" <td...@hotmail.com> wrote in message
> > >news:81b2ee4d-2753-42bd-b4c3-(E-Mail Removed)....
> > >>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!
>
Similar Threads
