Ask Your Question

How to record daily values of a cell

asked 2015-12-03 07:26:32 +0200

LoraineCooper gravatar image

updated 2015-12-14 15:17:59 +0200

Lupp gravatar image

Thank you please close this thread

Edit by Lupp (Demand not accepted):

The issue was described here as a recurring (not strictly periodic) failure of a very reasonable approch to the task by the OP. The failures occurred when opening a file containing the used formula and resulted in the replacement of "historic" values by 0 (zero). This if I remember correctly.

The formula, placed in B2 and filled down as needed, was similar to =IF(A2=TODAY();$OtherSheet.$H$12;B2), where A2 contained the date for which the memory should save the last value the source cell contained on that date.

I will also edit now my answer to include a not yet mentioned aspect of the problem.

edit retag flag offensive close merge delete


Now 2015-12-13 13:36:20UTC+1. How is it possible to update a question, originally posted 7 h in advandce of "now", on 2015-12-03? (Sorry. I simply cannot surrender to the date format actually used here by default.)

Lupp gravatar imageLupp ( 2015-12-13 13:39:42 +0200 )edit

@LoraineCooper : It is a pity! Your question including a clever approach to solve the underlying problem were worth reading. In addition my also rather thoroughly compiled answer gets difficult to understand without knowing deleted explanations. Deleting or closing threads considered "solved" is not a resonable strategy for a forum. This in specific if the topic is as well worded as in this case, backing other users searching for its keywords to find it. No repetitive thread needed then!

Lupp gravatar imageLupp ( 2015-12-14 15:00:28 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2015-12-13 13:06:49 +0200

Lupp gravatar image

updated 2015-12-14 15:30:56 +0200

(The above announced amendment first:) Updating formula-cells in hidden or simply not visible ranges may be deferred by Calc as long as they are not refered to by formulae in the visible range. Making such cells visible will catch up. For "history aware" cells this may cause problems. We observe a similar proble sometimes with respect to not updated 'Pivot Tables' or copied-to 'Sort' results. These also examples for the violation of what I may call the "equilibrium principle" basically obeyed by spreadsheets.

Original answer:

This is subject to speculations, I am afraid. The mandatory specification for the recalculation of OpenDocument conforming spreadsheets does not even mention one of the terms Iteration (except for a few standard functions using iterative methods internally) or Recursion.

We have to understand that allowing (possibly indirect) recursive or iterative use of formulae is completely Implementation Dependent under the blanket clause in subchapter 2.4 of the mentioned document. The first paragraph there at least demands a documentation accessible by users. However, I do not know one with the exception of the help text under "iterative references in spreadsheets" concerning the Setting of the Option. Firstly help texts are notoriously either undependable or outdated or both. Secondly the help again does not say anything about recursion (with the exception that the index delegates the term to "iterative"). Thus we have to consider that the feature exclusively is offered for purposes of iterative numerical calculations (hoping for convergence instead of proving it). Any nonnumerical usability of the feature, even if working correctly in a specific case should be considered an undocumented behaviour subject to changes without notice from version (build) to version.

My speculations now:

1) The version you are using is realy old given the breathless release plan of LibreOffice. More recent versions may avoid the malfunction you complain about.

2) I also experimented a lot with Conditionally Suspended Circular Reference (CSCR) even trying much more sophisticated formulae, but never relied on it for "production". My experiences with the kind of rather simple use you try are not that bad. My suspicion with regard to your complaint: There is a lot to initialise when a spreadsheet is opened. Under (un)certain conditions a group of cells may be recalculated before everything they may depend on is correctly initialised. Without the CSCR used, this is not of great meaning. A second call for recalculation will repair things without your noticing the intermediary fault. Not so for CSCR for obvious reasons!

3) If I urgently needed the feature I considered two possible solutions:

3.a) Write user code for the purpose despite the fact that "macros" come with a lot of disadvantages. It may also be not quite easy to avoid the problems mentioned under 2. this way.

3.b) Try the formula =IF(NOT(B1="");B1;IF($A1=TODAY();$AnotherSheet.$H$21;B1)) or, to allow for a way of clearing the memory without need of recreating the formula apparatus

=IF($A1="Clear!";"";IF(NOT(B1 ... (more)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-12-03 07:26:32 +0200

Seen: 67 times

Last updated: Dec 14 '15