Manual calculation of circular references in Calc?

English
, ,
#1

In Excel, I can set the workbook to automatically calculate, or manually calculate based on my input (e.g. with F9). As it is often used with circular references, this is very useful for creating, among other things, animations with graphs. Here are the relevant MS Excel options (v2007):

The key notion that I’m after is handling circular references based on
iterations. For example, let’s assume AutoCalculate is off (Tools->Cell
Contents->AutoCalculate [unchecked]), that Iterations are enabled with a step of
1 (Tools->Options->Libreoffice Calc->Calculate), and that I first put this into
cells A1 through B2:

  |  A  |  B
--------------
1 | dt =| 0.1
2 |  t =| 0.0

I now change cell B2 to the formula “=B1 + B2”:

  |  A  |    B
------------------
1 | dt =| 0.1
2 |  t =| =B1 + B2

But because AutoCalculate is off, the value shown should remain 0.0. Despite
the circular reference, if I do this exercise in MS Excel, I can press F9, and
B2 will update to 0.1. If I press F9 again, it updates to 0.2, and so on.

Currently, however, when I change the cell B2 as above, Calc first returns
‘#VALUE!’. When I then press F9, cell B2 then contains ‘ERR:523’.

How can I recreate manual calculation with circular (and self!) references with LibO?

#2

Menu/Tools/Cell contents.

It’s good to have a little review about what is there, using a new application.
And better a quick read at help file.

#3

No, this doesn’t seem to be what I want. I’m specifically after the functionality to handle circular references, such that I can get an iterative answer based on previous values of the cell. Let me edit the question …

#4

Ok, then see in Menu/Tools/Options/LibreOffice Calc/Calculation/Iterative references.

#5

Please see the edited wiki question, as I specifically mentioned that the iteration value is set to 1. If you are sure this works – with LibO 3.6 RC – then there must be something else I’m missing. Does the procedure I described above work for you?

#6

Adjusting the minimum change to 0.1, and use Ctrl+Shif+F9 (hard recalculation) to recalculate, works for me.

#7

I was not aware of Ctrl+Shift+F9. That indeed works for this contrived example. Thank you. Unfortunately, if I use the ‘t =’ line in other cells, or attempt a similar circular reference setup elsewhere – on a sheet that works in MS Excel – I’m having no luck. I’ll have to keep experimenting …

#8

sorry for commenting into an old thread,
iterations are not one of calc’s strongest capabilities,
they are somewhat unclear in definition and devs don’t like to work in this area,
they do work - mostly - for ‘solver problems’ where a result is requested and found by converging calculations,
but! they have weaknesses with nested dependency chains,
as well when trying to use them as counters or ‘piggy bank’ functionality,
e.g. as they mostly start with a twofold step,
i consider it wrong to use hard recalc as a tool against such weaknesses …

#9

Hi @hunteke,

Still looking for a solution to your problem?

Hmm… If Gnumeric has this capability but LibreOffice doesn’t, please file an enhancement bug and provide information about how the manual calculation should work. Don’t forget to mark your bug as an ‘enhancement’. The QA team will be happy to help you triage your feature request in the bugtracker.

Please post a link to any bugs you file in a comment below using the format “fdo#123456”.

Thanks!

#10

Yes. I’ve since found Gnumeric has this capability, but it is not yet as refined as Excel’s, nor does it have many other functions that are in LibreOffice’s repertoire.

#11

@qubit1 - You digged out a very interesting question. I looked at it an consider Calc’s behavior as correct.

Currently, however, when I change the cell B2 as above, Calc first returns ‘#VALUE!’. When I then press F9, cell B2 then contains ‘ERR:523’.

IMHO, a statement like “B2=B2+whatsoever” can only be made in a loop of a computer program, where the instruction is "take the current value of B2, add the value “whatsoever” and assing the newly calculated value to B2 as the new value.
However, in paper based or spreadsheet calculation such a statement is simply mathematically wrong. The describe EXCEL behavior is a bug.

#12

I disagree. Excel’s behavior is very much intentional, and is eminently useful in many physics, chemistry, biology, engineering, and math contexts. There are many problems that are not analytically tractable, and can only be solved through iteration. There are many educational contexts for this functionality as the ability to “pause” a function at each iteration allows for deeper student insight. (Even Gnumeric has this capability, albeit with a few “gotchas”.) No, this is a bug with Calc.

#13

@hunteke - I know that in many fields iterations are the only way to get solutions. Your usage of EXCEL with an interation step of 1 is nothing else but a computer program, which does 1 iteration step per key stroke. Although I did not test it, I assume you can make iterations in Calc using a macro and this should have the advantage of many iteration steps running automatically.

If you still feel that this is spreadsheet bug, please feel free and file a bug report.

#14

I agree entirely with hunteke. That ability of Excel is very useful, I’ve used it on several occasions in different contexts. I was stunned to find that both Libreoffice and OpenOffice don’t allow it. Essentially, it appears that neither will allow a calculation to refer to the existing value of a cell when recalculating it, a nuisance for a problem I have right now. Even if it is making a spreadsheet more like a computer program - Yes, you’re right - and it’s useful extension of capability.

#15

I am having the same problem. I have written a thermal storage program for heating a home with Off Peak electricity. It is recursive for tank temperature, with inputs and outputs going on at the same time. The operator must put in different values of volume and power to make sure there is enough energy to carry the home through the On-Peak heating time.

LibO works part of the time, then pops up with 200 Err:523 messages. Sometimes it will come back by changing a parameter, and sometimes is stays stuck. Haven’t found F9 to work.

Excel works all the time. Occasionally, it will get stuck, but it a manual calc command will get it going again.

I am preparing this program to go out to dealers for sizing calculations in the field. They are not EEs in computer science. I really don’t care whether this is a bug, mathematically irrational, or anything else. It works reliably in Excel and not in LibO. I would be happy if recalc worked from the menu pull down.

I like LibO for all the obvious reasons, but may have to abandon it because of “killer” points like this, [and the fact that Hyperlink quit working, which is another problem for my dealers]. Too many bugs/problems for my work flow.
…

OK, after the rant, I went to the preferences/LO calc/Calculate and changed the Iterations Steps to 1000 (Min Change is .001). If the iteration reaches its end point with no legal value, the Err message comes out. Expanding the range (Steps x Change) of the iterations seems to help. Maybe the LO programmers could detect illegal values and automatically reiterate until the numbers become legal.
…

One more. Sorry for the length. When I make a big change in a key number the interation crashes. Say the number is normally 1000 (gallons). If I type in 0, then I get #DIV/0! all over the place, which is normal. I them go back and put 1000 in the cell and hit return. Nothing happens. I go to Tools/Cell Contents/Recalculate and nothing happens - no matter how many time I go there (F9 doesn’t work, nor ctrl-shft-F9).

However, If I type 1000 in the cell again, it recalculates, giving me Err:523 the second time and the correct numbers the third time. So, it takes 3 recalcs to get back to normal. I cn live this this. But why doesn’t the recalc menu work? I would like to create a button on the screen that says “RECALCULATE”.

After going around and around with this, I finally discovered CMD-SHFT-F9, mentioned above and called “hard recalculate”, whatever that means. Any way, it does all the steps to get the iteration back to real numbers in one step. I am looking for an official definition of what it is. :slight_smile:

#16

@BenG - When I read the last 2 paragraphs I get the impression that we have a bug here. Why don’t you file a bug report using: https://www.libreoffice.org/get-help/bug/

#17

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.