keeping a running total in a single cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shrtdawg73 - 22 Aug 2006 20:36 GMT

Is it possible to have numbers added to the same cell and have excel continue
to calculate the addition for me in that same cell......ex: I have the number
8 in cell d2 and I want to add the number 8 to that cell and have excel add
the 8 to the previous 8 for a total of 16 in the same cell.....the next time
I would add 5, and the total would be 21? Can this be done in a single cell?

Reply to this Message

Don Guillett - 22 Aug 2006 20:50 GMT

right click sheet tab>insert this>SAVE> now in cell a5 you can add it up

Option Explicit
Dim oldvalue As Double

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$5" Then
On Error GoTo fixit
Application.EnableEvents = False
If Target.Value = 0 Then oldvalue = 0
Target.Value = 1 * Target.Value + oldvalue
oldvalue = Target.Value
fixit:
Application.EnableEvents = True
End If
End Sub

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Is it possible to have numbers added to the same cell and have excel
> continue
[quoted text clipped - 6 lines]
> I would add 5, and the total would be 21? Can this be done in a single
> cell?

Reply to this Message

Duke Carey - 22 Aug 2006 21:24 GMT

While Don's approach can solve your problem, keep in mind that you lose any
kind of audit trail. If you miskey a number, your running total is hosed and
there is nothing you can do but start all over. Matter of fact, even if you
DON'T miskey a number, you hav no way of validating that the running total is
correct.

In other words, what you want to accomplish isn't a recommended way of working

> right click sheet tab>insert this>SAVE> now in cell a5 you can add it up
>
[quoted text clipped - 23 lines]
> > I would add 5, and the total would be 21? Can this be done in a single
> > cell?

Reply to this Message

shrtdawg73 - 22 Aug 2006 21:58 GMT

thanks for the reply

> While Don's approach can solve your problem, keep in mind that you lose any
> kind of audit trail. If you miskey a number, your running total is hosed and
[quoted text clipped - 31 lines]
> > > I would add 5, and the total would be 21? Can this be done in a single
> > > cell?

Reply to this Message

shrtdawg73 - 22 Aug 2006 21:57 GMT

thanks for the information, very much appreciated

> right click sheet tab>insert this>SAVE> now in cell a5 you can add it up
>
[quoted text clipped - 23 lines]
> > I would add 5, and the total would be 21? Can this be done in a single
> > cell?

Reply to this Message

shrtdawg73 - 22 Aug 2006 22:27 GMT

Don, again thanks for your help, but I'm having a little trouble following
what you put. When I right click the sheet tab (at the bottom of the page)
and select insert, it comes up with a general tab and a spreadsheet solutions
tab. Does the formula that you wrote below start Option Explicit???? Is
there a formula that I can just assign to that specific cell......sorry if
this doesn't make sense.

> right click sheet tab>insert this>SAVE> now in cell a5 you can add it up
>
[quoted text clipped - 23 lines]
> > I would add 5, and the total would be 21? Can this be done in a single
> > cell?

Reply to this Message

Don Guillett - 22 Aug 2006 23:01 GMT

This is NOT a formula. It is a macro. A formula will NOT do what you desire.
I forgot to include a step
right click sheet tab>VIEW CODE>copy paste the code as written.
Although this WILL do what you said, Dukes comment is valid.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Don, again thanks for your help, but I'm having a little trouble following
> what you put. When I right click the sheet tab (at the bottom of the page)
[quoted text clipped - 31 lines]
>> > I would add 5, and the total would be 21? Can this be done in a single
>> > cell?

Reply to this Message

Gord Dibben - 22 Aug 2006 23:51 GMT

Don meant right-click on the sheet tab and "View Code"

Paste his code into that sheet module.

I still do not recommend this accumulator method, but caveat emptor.

Gord Dibben MS Excel MVP

>Don, again thanks for your help, but I'm having a little trouble following
>what you put. When I right click the sheet tab (at the bottom of the page)
[quoted text clipped - 30 lines]
>> > I would add 5, and the total would be 21? Can this be done in a single
>> > cell?

Reply to this Message

Gord Dibben - 22 Aug 2006 23:15 GMT

Are you sure you want to do this?

Think about it after reading the following.

You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.

Use at your own risk. I am Posting this just to show you how it can
be done, not as a good solution. You would be much better off to
have another column so you can keep track of past entries.

Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

Enter this in cell D4 and then in Tools>Options>Calculation check
Iterations and set to 1.

Now when you change the number in C4, D4 will accumulate.

Note 1. If C4 is selected and a calculation takes place anywhere in
the Application D4 will update even if no new number is entered in
C4. NOT GOOD.
Note 2. This operation is not recommended because you will have no
"paper trail" to follow. Any mistake in entering a new number in C4
cannot be corrected. NOT GOOD.

To clear out the accumulated total in D4 and start over, select D4
and Edit>Enter.

Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
which does what you want without the re-calculation problem, but
again there is no "paper trail" for back-checking in case of errors
in data input.

http://longre.free.fr/english/func_cats.htm

Also see John McGimpsey's site for VBA method.

http://www.mcgimpsey.com/excel/accumulator.html

Gord Dibben Excel MVP

>Is it possible to have numbers added to the same cell and have excel continue
>to calculate the addition for me in that same cell......ex: I have the number
>8 in cell d2 and I want to add the number 8 to that cell and have excel add
>the 8 to the previous 8 for a total of 16 in the same cell.....the next time
>I would add 5, and the total would be 21? Can this be done in a single cell?

Gord Dibben MS Excel MVP

Reply to this Message

shrtdawg73 - 22 Aug 2006 23:38 GMT

thanks for the help. I think what you explained is more of what I'm looking
for.

if I use the formula " =IF(CELL("address")="$C$4",C4+D4,D4) " (does it go in
the cell exactly like that) I can add a number to C4 and D4 will reflect the
total? I hope I'm understanding this right? :o)

> Are you sure you want to do this?
>
[quoted text clipped - 44 lines]
>
> Gord Dibben MS Excel MVP

Reply to this Message

Gord Dibben - 23 Aug 2006 00:17 GMT

Yes

Enter the formula in D4.

Enter a number in C4, D4 will show that number.

Overwrite the number in C4 with another and D4 will accumulate.

You can use any two cells for the input and accumulator.

Just change the cell references.

Be sure to read all the caveats included with this method.

Gord

>thanks for the help. I think what you explained is more of what I'm looking
>for.
[quoted text clipped - 51 lines]
>>
>> Gord Dibben MS Excel MVP

Reply to this Message