[[email protected]]

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
:

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

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
:

I'm sorry I dont think that works.

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


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

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.

[Spencer101]

Originally Posted by

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.

There are many ways of doing this, and Claus provided one of them (that 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 "total section" too and fully explain what it is you need then you will get a solution that works for you.

[[email protected]]

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
:

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

not in the newsgroup but you can look he

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

and then post here the link to the uploaded file.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

Thanks Spencer.

http://rapidshare.com/share/300A83FD...1A2E730BCFD764

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
:

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

Hi Claus,

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

http://rapidshare.com/share/8CECE8B3...EEB62B99FFB640

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
:

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[[email protected]]

Hi Claus,

Works perfectly thanks!