Description: To finish my spreadsheet-sequence I usually let my pupils write a little car-racing simulation in Excel. At my new school I haven't got Excel at my disposition, so we do everything with Calc (which is fine generally). However, there is something that worked fine in Excel that I cannot get to work in Calc. I need an accumulator field that adds the result of a dice-roll (simulated by =int(rand()*6)+1) to get the current sum of all prior dice-rolls plus the current one. This is possible if you activate iterative calculations. But with my current settings I most of the time get the following error: https://i.stack.imgur.com/XBpY8.png The settings are as follows: https://i.stack.imgur.com/H5XXb.png I can get the iterative calculation to work if I set "Minimum cange:" to 6. But when I press F9, the calculation is not correct. It always at least adds double the dice-value, but oftentimes a value that seems to have no connection to the show dice value. In Excel this simply worked using the following settings: https://i.stack.imgur.com/02GCR.png https://i.stack.imgur.com/Zr5ic.png Calc's behaviour seems to be wrong, no matter what settings I enter for "steps" and "minimum change". Steps to Reproduce: 1. set setting according to screenshot 2. add a circular reference 3. press F9 Actual Results: err:523 or miscalculations (seems like two iterations at a time) Expected Results: On every press of F9 the new "dice-value" should be calculated and added to the accumulator field. Reproducible: Always User Profile Reset: Yes Additional Info: User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:57.0) Gecko/20100101 Firefox/57.0
Works fine for me, setting up the steps to 1 and the Minimun change greater than the increment value.
I posted the problem on stackoverflow before. It seems, as if someone else can confirm the behaviour. Have a look at pnuts' comment on stackoverflow: https://stackoverflow.com/a/47842167/966986 For ease of use, I copy it here: I note m.a.riosv's comment to your bug report and suspect that individual either has a different set up or is using a different generator for the values in A1. Simplifying A1 to be an integer between 1 and 3 inclusive (keyed in not generated) shows the following (repeatable) pattern when set to 1 iteration and minimum 1: 0 0 1 2 2 Err:523 3 Err:523 1 14 2 Err:523 3 Err:523 1 26 2 Err:523 3 Err:523 1 38 I am now more convinced there is a bug, and you are correct about the doubling up. Also about the significance of the 'Minimum change' (ie a value is only returned when the change is from 0 or more than 1 (ie when drops from 3 to 1). Incidentally: Version: 5.4.3.2 (x64) Build ID: 92a7159f7e4af62137622921e809f8546db437e5 CPU threads: 2; OS: Windows 6.29; UI render: default; Locale: en-GB (en_GB); Calc: group (and also on an earlier LO version and on an OO version).
A temporary fix for the problem can be achieved using the following settings: Iterations: 1 Minimum change: 6 Then use a formula like this in the accumulator field (A2 in this case): =A2+(A1/2) This way no error occurs and the right dice roll gets added on pressing F9 (recalculate). Which in itself is a good indicator that something with the original behaviour is wrong.
You can't confirm your own bugs. Moving it back to UNCONFIRMED until someone else confirms it.
Please, provide your screencopies as attachment to this bug report, because we can not be sure that your links will be still available in the future. A test file would be great to make easier testing by others. Set status to NEEDINFO, please set it back to UNCONFIRMED once requested informations are provided. Best regards. JBF
Created attachment 138621 [details] A version of the game that shows the problem Cars drive double as fast as they should, because the dice rolls are added twice to the position.
Created attachment 138622 [details] Settings needed to make the game work at all It is necessary to set: Steps: 1 Minimum change: 6 otherwise the game often results in Err:523
Created attachment 138623 [details] Images from the original post The four images from the original post, so that they don't go missing.
Thank you for the test file. Works fine with versions 5.4.3 from Ubuntu PPA, 6.0.0.1.0+ and master build at home under Ubuntu 16.04 x86-64. Best regards. JBF
I see the issue with a simplest context. With a new blank spreadsheet Set up to use calculation with iterative references, default values 100 - 0,001 Introducing in A1: =A1+1 result Err:523 with values for iterative 1 - 6 result: steps increment of 3. Looks if the A1 was recalculated when is called by the formula, and again to obtain the formula result. Version: 5.4.4.2 (x64) Build ID: 2524958677847fb3bb44820e40380acbe820f960 CPU threads: 4; OS: Windows 6.19; UI render: default; Locale: es-ES (es_ES); Calc: group With LibreOffice 3.3.0 OOO330m19 (Build:6) tag libreoffice-3.3.0.4 [F9] doesn't work but reproducible with hard recalc. The same with AOo 4.1.1
*** Bug 117049 has been marked as a duplicate of this bug. ***
Thank you for that information you article http://potty-racers2.com
still bug: iteration is performed twice on initial set of A1: '=A1+1' or similar, (iterations: 1 minimum change >=1), result: '2', still bug: with above formula iteration doesn't star with former value of the cell, but with zero, still bug: on subsequent recalcs (strg-shift-F9) iteration is performed once if set to 1, twice if set to 2 or above, but not more than twice even when set to 1000, the original question works nearly as expected - as requested by the OP - with: Version: 6.2.8.2 (x64) Build ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee CPU threads: 8; OS: Windows 6.1; UI render: GL; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: threaded as well with the RAND() formula as with fixed values (which is easier to follow / easier to see how many steps are performed, use ctrl-shift-F9 or new input to the cell to trigger new calculation) fixed? intentionally? by chance? one thing left: if you start with '0' in A1, and enter the formula '=A1+1' in A1 with iterations set to 1 and minimum change set >= 1 i'd expect a result of '1' ... funnily it's '2', on subsequent calculations triggered by hard recalc calc steps through the numbers one by one, just the start is deviating, it doesn't matter with which value you start, if you replace any numerical value in a cell with a formula like '=A1+x' or '=SUM(A1)+x' or '=A1+B1' or '=SUM(A1:B1)' (x value to choose) the first iteration will not produce the expected result but the double value of the summand to add (in the samples x or B1), it looks as if the iteration doesn't start with the former value of the cell, but will instead use the value of x or e.g. B1 as a start value and perform the iteration once, or use 0 as start value and perform the iteration twice, similar applies for '-' calculations, while multiplications and divisions start with a zero value? new bug? one other thing irritating: the setting of 'Minimum change:' in the options looks more like working as the *maximum change* above which calc switches to Err:523, enhancement request? good to know: when the formula / iteration results in Err:523 the calculation is performed anyway, and the resulting value is stored and is used for the next iteration, Err:523 is inherited by dependent cells, and cleared once the conditions for the first cell are ok again, and even one more 'uncertainity' ... for A1: '=A1+1' the version below starts with two times adding 1 to zero, and on subsequent recalcs adds 1 if iteration is set to 1, and 2 if iteration is set to any higher value ... ??? there is still work left in this area ... Version: 6.5.0.0.alpha0+ (x64) Build ID: 9ab43aebad67383057d2cc3f754ce2193fa78b4e CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: applies to other versions too
Good work for a donkey. Do you understand what you wrote? In maths, here are three types of equations: determined, undetermined and impossible. I have translate "determinata" in determined, but I am not sure if it is correct. A1=A1+1 is an impossible equation, namely it has not a solution.
Hi Carlos it's fine rethink before posting. To use this kind of expression the option for iterative references must be enable, it is there just for that. https://help.libreoffice.org/6.4/en-US/text/shared/optionen/01060500.html?DbPAR=SHARED#bm_id3149399
Not considering the fact that you want try a solution to an impossible equation (nearly there is not solution), you must set minimum change to very high value. I am the person that, with a my bug, has done change the Libre Office version to 6.2...., to solve the problem the correct loop number. B1=A1+B1 is a determined equation and it operates as a piggy bank. Starting from A1=0 and B1=0 (piggy bank empty), if you put in it 10 Euro (A1=10), you have B1=10 (final data in piggy bank). Then you must reset A1, before to have another enter. Minimum change must be higher than money put in piggy bank, to avoid the err:523.
sorry, long ... @carlo: i highly appreciate the work you invest to bring LO forward, reg. iterations a 'dummies' sight: recursive dependencies / formulas can have 'a solution', 'no solution', 'plenty solutions' - in real life. calc isn't aware of 'plenty solutions', it tries to compute one 'converged' 'narrowed' result, and throws err: if it cannot find one in the limits given by the settings. A1: '=A1+1' has no solution in real life, but has one in calc with 'iterations' enabled. it has more than one depending on the start value and the settings for iterations. it's something like a 'state machine' with the result depending on the state before the calculation and the 'new round of calculation'. users use this 'feature' to do simulations as a.burger in the initial post. one can call it a misuse of a functionality. but the formula A1: '=A1+1' can be used as a counter or similar ... e.g. for the laplace calculation @jean-philippe.grivet did in https://bugs.documentfoundation.org/show_bug.cgi?id=46850, adding a cell as a counter could show how many iterations are needed to come out of the err: conditions, and how many to get a stable result ... but for uses like this a function needs to be 'understandable to simple minded users' ... and that might also help to get 'pros' and developers to gain understandable results for their complex tasks. B1: '=B1+A1' has the same problem, there isn't one distinct solution, but infinite depending on start values and settings for iterations, calc in some way is neglecting start values ... :-( the combination A1:'=C1', B1:'=A1+10', C1:'=B1-10' does have plenty - infinite - solutions in real life, calc doesn't check that but produces one distinct result (with iterations enabled). one can play with different values entered in A1 and then replacing that with '=C1', i don't know if it's useful for any purpose, but it's nice to see how calc and the iteration settings work. the combination A1:'=C1', B1:'=A1*1,1', C1:'=B1/1,11' does have only one solutions in real life, 0 | 0 | 0 , calc tries to come narrow to that and needs different time reg. the value you start with (A1: '10' -> A1: '=C1'), it takes some 10.000 rounds to come near to '0', different for different setting of cell format, if you work with scientific format you can count about +100k iterations to come below an error of 1^e-307 (smallest value setable) and need more than one recalc for that as calc doesn't accept values above 32767 for iterations. i don't know if it's useful for any purpose, but it's nice to see how calc and the iteration settings work. i do not propagate using things that way, but they may help to clarify how calc works, and thus understand the complaints made in many bug reports reg. recursions / iterations 'not work' or 'wrong results'. i'd like any 'understandable behaviour', especially one that's in harmony with the documantation. - thus doing two additions on the intial insert of a formula and one - acc. to iterations set to 1 - on every recalc is ... a bug? at least it's something to mention and be aware of. (there had been something in the code with 'performing the calculation two times per iteration', that's out, i assume it's still in for the initial round.) - as well as not starting with the previous value of a cell when inserting a recursive or self referencing formula ... it works different if a formula references itself directly, or via a circle. - from playing with formulas like A: '=A+B', and B: '=A+B' somewhere in the sheet i estimate that the final decision about the order of calculation - if no other things e.g. dependencies apply one - is done from right to left and then bottom up, and not! 'left to right and then top down' or 'top down then left right'. that's contrary to the workflow / thought structure of plenty (most? all?) users - in occidental cultures! - maybe we see here how the thought structure of an oriental programmer is traced to the surface ... that's fascinating, and could eventually explain that we have more complaints from occidental users ... - if allowed calc treats two rounds of calculation before stop and see difference is within limits, to limit to one circle you have to set iterations to '1', despite that setting on initial placing of formula two rounds will be performed ... thats - mostly - not a problem for users seeking a solution for a converging formula, - but for others who misuse the feature to implement counters, piggy banks or similar, reg. b.
Dear a.burger, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug