accumulation formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know if there is a way to create a formula to accumulate changing amounts in one cell???
 
Hi
not quite sure what you're trying to achieve. Can you please give more
details (example data)
 
I need to manually put January totals in to cell B1, C1 will calculate that total. The next month I will manually add February totals into cell B1 - I need C1 to remember January's total and add it to February's total. I'm not sure if this is possible

Any help would be greatly appreciated

Thanks,
Chri
 
Hi Chris
see your response in Excel.misc (using VBA)
P.S.: please don't multipost as it scatters your answers
 
Hi Chris
the website I posted you includes just this kind of code to accomplish
that.
Have you looked at it?
 
I'm not having any luck finding it.... Maybe I'm missing something...(??)

I'm totally appreciating all your help though....

Chris

----- Frank Kabel wrote: -----

Hi Chris
the website I posted you includes just this kind of code to accomplish
that.
Have you looked at it?
 
Hi Chis

put the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "B1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("C1").Value = Range("C1").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub
 
Cris

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

Having said that, check out J.E. McGimpsey's site for VBA methods.

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

I believe Frank K. has previously given you J.E.'s site URL

Gord Dibben Excel MVP
 
Sorry about that John.

Your site is fine.

I got you and Stephen B. mixed up. Sent OP to Stephen's site.

Didn't I do this once before?

Gord
 
TY for the good accumulation formula.

In reverse, Is there a formula available that can put numbers that ar
entered into a single cell into a column?

ex. data is entered into A1 and the data is shown in column B1,B2
B3,etc. I will be using the accumulation formula given here and wan
to have a history of the numbers that go into the accumulated total
this will be my "paper trail".

( I am getting numbers from a data feed into a single cell, so I do no
have the option of taking a column of data and adding it for totals)

TIA
rai
 
rain

You could use a worksheet_change event macro to enter the contents of the
input cell into column B at the next available empty row.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
If Target.Address = "$A$1" And Target.Value <> "" Then
ActiveSheet.Cells(Rows.Count, 2).End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
stoppit:
End Sub

Right-click on your sheet tab and select "View Code". Copy the above code
into the module that opens.

Using A1 as the input cell, any new number entered will be automatically
placed into Column B(starting at B1)at the next available empty row.

I would enter in C1 =Sum(B:B) or =Sum(B1:B500). Whatever you think you need
to gather all future values in Column B.

Now you have a "paper trail" and a Totalizer cell(C1)

Note: if a mistake is made in last entered number in A1 , you will have to
delete the contents of the last cell in Column B then re-enter in A1.

Gord Dibben Excel MVP
 
rain

Correction to code.

As written, the number entered into A1 will show in B2 then B3, B4 etc.

Change C1 to reflect that and use =Sum(B2:B500)

Gord
 
Gord, noted and the change is made. It works just as needed. This i
extemely helpful and the last part of my project.

Can I make a change to the code to change the cell that accepts th
data to allow it to handle a range of cells?

ex. data is received in cells A1:A10 and displayed in columns B:B thr
K:K?

The cells that receive the data and do the "paper trail" will b
reviewed periodically and deleted.

Also, for another project that can not accept a "paper trail" Can th
code for the accumulation formula also receive data in cells A1:A10
and count accumulated data in B1:B100?

Thank you again

rai
 
rain

Try this for the range of A2:A11

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
For Each cell In Application.Intersect(Range("a2:a11"), Target)
n = Target.Row
ActiveSheet.Cells(Columns.Count, n).End(xlUp) _
.Offset(1, 0).Value = Target.Value
Next

stoppit:
End Sub

Not sure what is required for second part of question.

Gord
 
For ease of keeping track of each column of numbers I would be tempted to
enter Titles in Row 1

i.e in B1 enter A2(as text), in C1 enter A3

Gord
 
Back
Top