CumSum a single Cell that is dynamically changing

[wnakhoul]

Hi All,

I have the following situation in Excel and hope that someone can post a clean VBA solution to it (I am guessing a Worksheet_Calculate procedure after declaring some DIMs and CONSTs, Option Explicit, Option Base 1, Option Compare Text, DisableEvents etc...).
The situation is as below (hope to explain it as clearly and accurately as possible to facilitate the solution):

* I have a open excel worksheet (Call it worksheet A)
* One cell in Worksheet A (call it Cell X) has a formula
* Cell X formula is linked to another formula which is ultimately linked to an external data feed.
* As a result of the external data feed and as long as the data feed connection is ON, Cell X is dynamically changing throughout the day.
* Cell X value changes fluctuate between 0, positive and negative numbers (whole integer numbers i.e. no decimals. Example, 0, 803, -3,205, 0, 10,502 etc...)

Having described the situation above, I am seeking a VBA Private Sub Worksheet_Calculate() procedure that:

* CumSum all the positive value changes in once Cell (call it Cell Y). Cell Y can be in worksheet A or a separate worksheet.
* CumSum all the negative value changes in once Cell (call it Cell Z). Cell Z can be in worksheet A or a separate worksheet.

Many thanks in advance.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: CumSum a single Cell that dynamically changing
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Peter_SSs]

When the new data feeds into the workbook and cellX recalculates,
- is it possible that the recalculation could result in the same result result in cellX? That is, if cellX is, say, 100 and new data arrives, is it possible that cellX again calculates to 100?
- if the answer to the above is 'yes', should cellY have 100 added to it's total again?

 

[wnakhoul]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: CumSum a single Cell that dynamically changing
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Tnx Fluff. I shall comply with rules in the future

 

[wnakhoul]

When the new data feeds into the workbook and cellX recalculates,
- is it possible that the recalculation could result in the same result result in cellX? That is, if cellX is, say, 100 and new data arrives, is it possible that cellX again calculates to 100?
- if the answer to the above is 'yes', should cellY have 100 added to it's total again?

Except 0, for as long I have fed data into the workbook, I have never seen same number in the cell twice. Cell fluctuates between 0, then number (+ or -), then back to 0 waiting for new number, then new number (+ or -). If it did happen (although highly unlikely), yes add the number again to the cumsum value (Y if positive and Z if negative)

 

[Peter_SSs]

I have never seen same number in the cell twice.

For the moment, if we assume no repeating number in successive calculations then you could use a storage cell somewhere (lets call it cellQ, could be hidden) with worksheet_calculate code like this

If cellX.Value <> cellQ.Value then
  If cellX.Value > 0 Then
    cellY.Value = cellY.Value + cellX.Value
  Else
    cellZ.Value = cellZ.Value + cellX.Value
  End If
  cellQ.Value = cellX.Value
End If

However, this would not pick up the case if cell X did happen to recalculate to the same value as last calculation.
If you want to be sure to trap that as well, I think that you would need to keep tracing back the precedents of cellX, as I started leading you through earlier, until you identify all cells with data feed entry or manual entry or vba entry that can cause cellX to recalculate and use worksheet_change event codes on that/those worksheet/s to identify if those precedent cells/ranges have changed and, if so, run the post #2 style code.