Help with last 7 days running total

[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

 

[Claus Busch]

Hi,

Am Mon, 16 Sep 2013 02:10:47 -0700 (PDT) schrieb
(e-mail address removed):

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.

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


Regards
Claus B.

 

[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.

 

[Claus Busch]

Hi,

Am Mon, 16 Sep 2013 04:17:41 -0700 (PDT) schrieb
(e-mail address removed):

I'm sorry I dont think that works.

do you think that it doesn't work or have you tested it?


Regards
Claus B.

 

[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.

 

[Zaid.Nadat]

Hi Spencer,

Is there anyway for me to upload a workbook onto here?

Thanks

 

[Claus Busch]

Hi,

Am Mon, 16 Sep 2013 08:54:55 -0700 (PDT) schrieb
(e-mail address removed):

Is there anyway for me to upload a workbook onto here?

not in the newsgroup but you can look here:

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

and then post here the link to the uploaded file.


Regards
Claus B.

 

[Zaid.Nadat]

Thanks Spencer.

http://rapidshare.com/share/300A83FDD0AE899D481A2E730BCFD764

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.

As i'm sure you can understand I had to strip out all the sensitive data.

Thanks in advance

 

[Claus Busch]

Hi,

Am Mon, 16 Sep 2013 09:35:35 -0700 (PDT) schrieb
(e-mail address removed):

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.

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.

 

[Zaid.Nadat]

Hi Claus,

Thanks for that. It works perfectly in the sample spreadsheet but not in my actual one.

http://rapidshare.com/share/8CECE8B35F481BC5CDEEB62B99FFB640

The above link is the actual spreadsheet minus sensitive data.

If you could please provide me with a formula for this then that would be great.

Sorry for the inconvenience.

Thanks

 

[Claus Busch]

Hi,

Am Mon, 16 Sep 2013 10:10:21 -0700 (PDT) schrieb
(e-mail address removed):

If you could please provide me with a formula for this then that would be great.

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.

 

[Zaid.Nadat]

Hi Claus,

Works perfectly thanks!