Circle Reference
 

I have 2 data columns. In column A, the data is updated every day so
that the bottom cell of the column contains the new daily data.

eg:

DAY

1
2
3
4
5

So as each day goes by, the earliest day (day #1) gets dropped and I
only have data for the 5 previous days. What I want is to keep a record
of the data that I delete. So I want to keep a running total of all the
data entered through all the days. So if I delete day #1 data, I can
ensure that it gets kept on a running sum. For example, let's say the
data looks like this:

DAY DATA

1 4
2 5
3 6
4 7
5 8

So the running total of the data is (4+5+6+7+8) 30. So when tomorrow
comes, I will delete day #1 data and move day 2-5 data back, so new
data gets entered into day 5. That means that 4 in my data table will
be erased and the new sum will not be 30. How can I keep a running
total of all old and new data entered in that column?

In old programming software I would simply put sum=sum+x. This would
automatically keep record of the old sum and add the new entered data.

Anybody know how this works in Excel?


--
leafsfan1967
------------------------------------------------------------------------
leafsfan1967's Profile: http://www.excelforum.com/member.php...o&userid=24357
View this thread: http://www.excelforum.com/showthread...hreadid=384411

Circle Reference
 

What you are asking to do is possible but by deleteing all of the old days
you loose any kind of an audit trail. If something goes wrong you will never
know it and there will be no way to verify what you have (or at least not
easily). Why not keep all of the source data on a hidden sheet and then just
pull the last 5 days onto the display sheet. At one line per day this file
will not get so large that you can't deal with it. Just my two cents.
--
HTH...

Jim Thomlinson


"leafsfan1967" wrote:


I have 2 data columns. In column A, the data is updated every day so
that the bottom cell of the column contains the new daily data.

eg:

DAY

1
2
3
4
5

So as each day goes by, the earliest day (day #1) gets dropped and I
only have data for the 5 previous days. What I want is to keep a record
of the data that I delete. So I want to keep a running total of all the
data entered through all the days. So if I delete day #1 data, I can
ensure that it gets kept on a running sum. For example, let's say the
data looks like this:

DAY DATA

1 4
2 5
3 6
4 7
5 8

So the running total of the data is (4+5+6+7+8) 30. So when tomorrow
comes, I will delete day #1 data and move day 2-5 data back, so new
data gets entered into day 5. That means that 4 in my data table will
be erased and the new sum will not be 30. How can I keep a running
total of all old and new data entered in that column?

In old programming software I would simply put sum=sum+x. This would
automatically keep record of the old sum and add the new entered data.

Anybody know how this works in Excel?


--
leafsfan1967
------------------------------------------------------------------------
leafsfan1967's Profile: http://www.excelforum.com/member.php...o&userid=24357
View this thread: http://www.excelforum.com/showthread...hreadid=384411

Circle Reference
 

OK, I agree with Jim, but here goes:

In Row 6: Sum =sum(B1:B5)

Each day, do this:

Copy B6 to B7 (formula will look like Sum(B2:B6) ... don't worry about it
Copy B6 and paste back to B6 as Values
Copy B2:B5 to B1 (B4 and B5 will both have prior day's data, don't worry
about it)
Enter the new data in B5 (Now you're data's cool, just the sum to worry
about)
Copy B7 to B6 (Your formula in B6 now looks better!)
Delete B7

If you really gotta, go ahead and record a macro to do this, but it would
take me like 15 seconds total to do manually.



"leafsfan1967"
wrote in message
news:leafsfan1967.1ro6mj_1120521935.0929@excelforu m-nospam.com...

I have 2 data columns. In column A, the data is updated every day so
that the bottom cell of the column contains the new daily data.

eg:

DAY

1
2
3
4
5

So as each day goes by, the earliest day (day #1) gets dropped and I
only have data for the 5 previous days. What I want is to keep a record
of the data that I delete. So I want to keep a running total of all the
data entered through all the days. So if I delete day #1 data, I can
ensure that it gets kept on a running sum. For example, let's say the
data looks like this:

DAY DATA

1 4
2 5
3 6
4 7
5 8

So the running total of the data is (4+5+6+7+8) 30. So when tomorrow
comes, I will delete day #1 data and move day 2-5 data back, so new
data gets entered into day 5. That means that 4 in my data table will
be erased and the new sum will not be 30. How can I keep a running
total of all old and new data entered in that column?

In old programming software I would simply put sum=sum+x. This would
automatically keep record of the old sum and add the new entered data.

Anybody know how this works in Excel?


--
leafsfan1967
------------------------------------------------------------------------
leafsfan1967's Profile:
http://www.excelforum.com/member.php...o&userid=24357
View this thread: http://www.excelforum.com/showthread...hreadid=384411