asked 2012-07-26 23:36:59 +0200
This post is a wiki. Anyone with karma >750 is welcome to improve it.
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?
answered 2012-07-27 00:29:14 +0200
This post is a wiki. Anyone with karma >750 is welcome to improve it.
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.
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 ...
hunteke ( 2012-07-30 02:22:12 +0200 )editHi @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!
answered 2013-05-21 15:49:11 +0200
This post is a wiki. Anyone with karma >750 is welcome to improve it.
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. :)
@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/
ROSt52 ( 2013-05-22 07:38:15 +0200 )editanswered 2013-03-14 04:15:07 +0200
This post is a wiki. Anyone with karma >750 is welcome to improve it.
@qubit - 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.
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.
hunteke ( 2013-04-06 09:44:02 +0200 )edit@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.
ROSt52 ( 2013-04-08 03:25:41 +0200 )editLibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-07-26 23:36:59 +0200
Seen: 573 times
Last updated: May 31
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
