Help with last 7 days running total

Z

Zaid.Nadat

Hi,

I have a set of data as follows:

B C
Date Clicks
12-Jun 202
13-Jun 248
14-Jun 340
15-Jun 81
16-Jun 46
17-Jun 250
18-Jun 500

I want to set up a formula for a running total for the last 7 day so that as I add a row of data the running total auto updates.

Thanks
 
C

Claus Busch

Hi,

Am Mon, 16 Sep 2013 02:10:47 -0700 (PDT) schrieb
[email protected]:
B C
Date Clicks
12-Jun 202
13-Jun 248
14-Jun 340
15-Jun 81
16-Jun 46
17-Jun 250
18-Jun 500

I want to set up a formula for a running total for the last 7 day so that as I add a row of data the running total auto updates.
Click to expand...

in D2 try:
=IF(OR(B2="",ROW(B2)<MATCH(0,B:B,-1)-6),"",SUM(D1,C2))


Regards
Claus B.
 
Z

Zaid.Nadat

I'm sorry I dont think that works.

Just to provide some more information - This is just an extract from the data I have. I have over 200 lines of data and I add a line of data every day. I have set up a section below the data that I would like to provide me with a running 7 day total.
 
Z

Zaid.Nadat

I tried it and it didn't work.

My data has more columns. What I showed was just a little extract.

I have a 'total' section where I summarise the last 7 days which is situated below the actual data and it is here where I want the moving 7 day average to auto update as opposed to me having to manually update the range.
 
S

Spencer101

I tried it and it didn't work.

My data has more columns. What I showed was just a little extract.

I have a 'total' section where I summarise the last 7 days which i
situated below the actual data and it is here where I want the moving
day average to auto update as opposed to me having to manually updat
the range.
Click to expand...

There are many ways of doing this, and Claus provided one of them (tha
incidentally, does work for me).

Which is the best way for you will very much depend on your data layout
Perhaps if you provide us with sample in a workbooks showing the "tota
section" too and fully explain what it is you need then you will get
solution that works for you
 
C

Claus Busch

C

Claus Busch

Hi,

Am Mon, 16 Sep 2013 09:35:35 -0700 (PDT) schrieb
[email protected]:
Well what I am asking is how would I set it up so that the box filled in red will show a running 7 day average as I will be inputting a line of data daily.
Click to expand...

sum of the last 7 days:
=SUM(OFFSET($B$1,MATCH(MAX($A:$A),$A:$A,0)-1,,-7))
average of the last 7 days:
=AVERAGE(OFFSET($B$1,MATCH(MAX($A:$A),$A:$A,0)-1,,-7))


Regards
Claus B.
 
C

Claus Busch

Hi,

Am Mon, 16 Sep 2013 10:10:21 -0700 (PDT) schrieb
[email protected]:
If you could please provide me with a formula for this then that would be great.
Click to expand...

in D107:
=SUM(OFFSET($C$1,MATCH(MAX($B:$B),$B:$B,0)-1,,-7))
in D108:
=AVERAGE(OFFSET($C$1,MATCH(MAX($B:$B),$B:$B,0)-1,,-7))


Regards
Claus B.
 
Top