adding formula results together

[Trussy1]

I have a piece of software that has an excel link function which is fine. But I have a column of cells which have constantly changing values in. I need to add these values together to keep a running total. I am pretty sure the values are not the result of formulas more likely to be the way the software is fetching teh data through an API from the web. Bit like watching share prices change.

Here's hoping and thanks to anyone wishing to take on this headache.

Many thanks

Trussy1

 

[Ruddles]

Hi and welcome to the board.

If you simply wish to display the sum of a column of numbers on your worksheet, the SUM formula will do the job. If you know the exact range, it's:-

=SUM(A1:A999)

or if you on;yl know that the numbers are in column A but not how far they extend, it's;-

=SUM(A:A)

(Put the first one anywhere in your worksheet; the second one can go anywhere except coumn A.)

As long as you have calculation set to automatic, the formula should recalculate whenever the numbers change.

Is that what you mean?

 

[adriana25]

please help, i want to do this formula in excel in the same worksheep if is possible. for example in the cell A1 i have a number 5000. the formula have to do like this: until 2100*7%=147; the difference(2900 * 18%=522), and then i have to aditionate 147+522=669; all thi in a one cell

Thank you very much

 

[Trussy1]

Hi Ruddles. Thanks so much for teh quick reply. Obviously I did'nt explain very well. If you imagine say column d2:d20. Each cell, D2, D3 etc has a constantly chaning value. What I need to be able to do is sum the changing vales either in the same cell or adjacent one. So say D2 starts with 20, the next value is 50, so the sum is 70 etc etc.

Does that explain a bit better?

Rgds

Trussy2

 

[Ruddles]

please help, i want to do this formula in excel in the same worksheep if is possible. for example in the cell A1 i have a number 5000. the formula have to do like this: until 2100*7%=147; the difference(2900 * 18%=522), and then i have to aditionate 147+522=669; all thi in a one cell

Hi Adriana and welcome to the forum.

You have entered your question in someone else's 'thread' - where someone else has entered a question and where they're expecting others to respond.

Please start your own thread by clicking the New Thread button on the forum main screen.

 

[Ruddles]

Trussy, I think you would need to use the worksheet's 'change' event handler to detect changes in that range of cells, then add the newly-changed value to an 'accumulator' somewhere.

Try this code: it detects changes in D2:D20 and accumulates the numbers in E2:E20. (We can change that later easily enough.) Create a new workbook and set the background colour of D2:D20 to yellow and the background colour of E2:E20 to green (or somesuch). Then right-click the worksheet tab and look for Sheet1 in the Project Explorer (top-left), double-click the name Sheet1 and paste this code in the code window which opens up:-

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim rDataRange As Range
  Dim rCell As Range
 
  Set rDataRange = Range("D2:D20")
 
  If Not Intersect(Target, rDataRange) Is Nothing Then
    For Each rCell In Intersect(Target, rDataRange)
      Application.EnableEvents = False
      rCell.Offset(0, 1) = rCell.Offset(0, 1).Value + rCell.Value
      Application.EnableEvents = True
    Next rCell
  End If
 
End Sub

Now enter some numbers in D2:D20 and watch them accumulating in E2:E20. Try pasting a series of numbers in D2:D20 and check they're accumulated correctly.

Does this work? If so, we can look to adding the code to your real workbook.

 

[Trussy1]

Hi Ruddles. Good and bad news. Yep it works a treat when I either copy and paste numbers in, or type tehm in directly, the value keeps accumalating in E3. But, and this might be a little difficult to explain. The best way I can explain what is happening is......
Assume the data is coming in on sheet 1 of the workbook, cell C2 (and is a constantly changing value). Also assume I have pasted your formula into the code on the page of sheet 2 of the workbook. In cell D2 (Sheet 2) I type in '=Sheet1!C2). Fine, a value appears (for example a value of 10), this appears in E2. Then it stops. The values keep changing in cell D2, but only the first value it saw is in E2.

Sorry Ruddles does that make sense?

It's as if it see's the first value then ignores the rest.

Any thoughts.

Again my very best regards and thanks

Trussy

 

[Trussy1]

Just a thought Ruddles. Every time I drag down from D2 and copy the =Sheet1!C3, down the D2:D20 column the values in E2 - E20 all change and add up as they need to. I was wondering if there is any way a timer could be coded into the formula you sent so that D2:d20 is automatically refreshed every 10 milliseconds or so?


Just a thought. Unless you have a different solution.

Regards and thanks

Trussy

 

[Ruddles]

So let me check I've got this right...

The values are coming in to Sheet1!C2:C20. You have formulae in Sheet2!D2:D20 which reference these cells, so in Sheet2!D2 you have =Sheet1!C2, etc, down to Sheet2!D20 which contains=Sheet1!C20.

Is that correct?

In the finished worksheet you want Sheet2!D2:D20 to contain the most recent values from Sheet1!C2:C20 and Sheet2!E2:E20 to contain the aggregated values.

Yes?

 

[Trussy1]

Yes thats spot on Ruddles. Any more info I can help with?

Rgds and thanks

Trussy