Starting and stopping equations in specific cells in excel

[GarethmMorgan]

Hi Guys,

I have a number of equations within a table in excel which calculate using the current time, and I would like to be able to stop one equation running, and start a second equation, but without loosing the final value displayed within the cell from the first equation.
I have seen that you can stop all calculations from being automatic within "calculation Options" but I have other equations simultaneously running which i need to keep going throughout as this other column of equations starts and stops. Hope that makes sense.

thank you in advance,

regards,

Gareth

[ben_hensel]

Fundamentally what NOW() does, is whenever the function runs, the function goes and asks the motherboard what the current clock-time is, and uses that.

It doesn't have any way to "remember" what the cell was, it will recalculate every time you tell the sheet to recalculate.

You're probably better off instead making it a reference a cell, and using that cell to hold a timestamp. Fill the timestamp using CTRL+SHIFT+: (that's control, plus shift, plus the colon key all at once, as a keyboard shortcut); another option is to bind a tiny little macro (Allen Wyatt explains it) and bind that to a button or something, which you click to reset the timestamp.

[GarethmMorgan]

Hi Ben,

Thank you for your reply, Essentially what i am trying to do is show the volume of fluid remaining over an unspecified time, when knowing the volume loss per minute and the initial starting volume. What i have is in cell A1, I have a clock with the current time, in cell F12 i have a static time of when the equation started, and in cell G12 i have the equation which counts down the volume remaining as a live value from the specified initial volume, using the difference between the start and current time and the volume loss per minute. Ideally i would be able to stop the equation "at the push of a button" and the cell would remain the shown value when the equation stops. I hope that makes sense with what i am trying to achieve.
thank you

Gareth

[ben_hensel]

Are you using VBA to keep it "animated" instead of just updating calculations when the workbook's state changes or what? Then click the button to tell the VBA to stop updating. It really depends on the specifics of how you're getting Excel into making a video for you.



Also this is not relevant to the question precisely (it's something I just happen to know from IRL), but flow rate will probably not be constant over time -- if it's emptying from gravity it will be dependent on height, and if it's pumped it will be dominated by the pump curve. I mean if it's something like a small change in net height over the time interval it might be a negligible change in flow rate (maybe if you're, like, slowly draining a pool or something), so you might get away with approximating it as constant, but maybe not?

[GarethmMorgan]

yeah using vba to keep the clock "ticking" to show a live time reading, and the application is to predict fuel usage in a race car, so the fuel is being pumped out at a constant flow rate over time, the flow rate will differ depending what Calibration mode the engine is in, fuel saving, yellow flag, pit ect, but i can account for that within another piece of code, but essentially trying to predict the fuel remaining in the car as a live value.