Variable that is constantly changing

[coys1717]

Hello, I'm not an Excel expert and unfortunately I need to go down this route as the simple route never actually existed, such is life.

Ive been looking at numerous tutorials and everything is a blur, so I'll explain the problem briefly before I ask the question.

I have a workbook connected to a live datafeed, I need to design a formula which can calculate a certain part of that data and return the results in the next cell along. The data is numerical and is updated every second, what I want to do is work out how much the data is increasing by every second, this will happen across multiple cells.

I need the: total of x + 1 second, then new total of x - original total of x on a loop.

Hows the syntax worded to assign an integer whose value is constantly changing?

[pjwhitfield]

I may have read that incorrectly but cant you use the Worksheet_Calculate event to total the amounts and populate the relevant cell?

[JBeaucaire]

What PJWhitfield is saying is you CAN use a Worksheet_Calculate() event to accomplish this. That event will trigger itself anytime the sheet calculates anything. So you could design the macro to grab the value(s) of any key cell(s) and record them in a log on another sheet for later analysis. A simplistic example:

Please Login or Register  to view this content.

That should get started on developing it for you workbook. This event macro will reside in the Sheet Module itself.

[coys1717]

Many thanks, rather than logging it into a separate spreadsheet, I was looking for cell "A" and "B" to update simultaneously, cell "A" does this automatically so I was hoping that I could simply apply a formula to cell "B" to get the results. But without learning Excel front to back I believe this is going to go way beyond my capabilities.

Many thanks

[JBeaucaire]

If you put a formula in cell B that references cell A, it should update every time cell A does.

[pjwhitfield]

if you're looking to see how much cell A has changed then you'll need to store the value of A before it changes and then compare.

eg Cell A1 is updated every second and you want the amount of increase/decrease to be in Cell B1.

Second1 A1 = 100
Second2 A1 = 150

to be able to calculate on A1 changing you'll need to store second1's value of A1 before A1 changes...something like this (very basic)

Please Login or Register  to view this content.

[coys1717]

Many thanks PJ, your absolutely spot on, the value does need to be stored temporarily to make the calculation, the maths to get the total is beyond easy, but the coding for a newbie is tricky.

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.