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
(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.
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.
 
C

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.
Click to expand...

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


Regards
Claus B.
 
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
(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.
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
(e-mail address removed):
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to determine the value? 2
14 Day Average REVISITED 4
uneven ranges and coding 6
Count Dates in a range based on Column Header Id without using sum-product 1
Combo Bar-Line Graph with single list of data 1
Looping through data with calculations 1
Pivot running total % 8
Help!! find two number recurring numbers in row?? 1

Top