Description: I put below the steps to reproduce the error on LIBRE. Tools -> Options -> LibreOffice Calc -> Calculate -> Click on Iterations -> Steps from 100 to 1 and Minimum Change from 0,001 to 1000000, then 0 in A1 and in B1; then, starting from B1 in the insertion cell, put =B1+A1, (If you have error, try again with some click in the condition of Calculate), finally put 100 in A1 and Enter; then 0 in A1 and Enter. Unfortunately the result is B1=200, instead of 0+100=100. Why was the error not shown? Because to do 100 or 101 iterations to reduce the error under 0.001 is not important. With the settings done, to do 1 (minimal value) or 2 iterations means to have the severe bug. Best regards Carlo Sarti Steps to Reproduce: See above Actual Results: See above Expected Results: See above Reproducible: Always User Profile Reset: No Additional Info: If necessary, I can attach the file where I use the circular references (definition for Excel). User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; LCTE; rv:11.0) like Gecko
I do reproduce the described behavior. But I can't confirm the bug because I don't know if it's actually a bug. What is the meaning of what you have done here? The iterative calculation of values that have circular references to themselves is meant to bring a result that can be obtained by incrementally approaching; that means that there should be some guessed value, and some algorithm (designed as the formulas in cells) that comes closer each step. The number of steps and the precision value are something taken using rinse-and-repeat approach, or from experience... and now you take a task that has no meaning and that is not converging, put some values to avoid any meaningful processing, and call it a "severe bug". At the very least, please describe *why* do you believe that the result must be as you think it must be, and not as it is (remember, that it's just an initial guess that should be improved incrementally iteratively afterwards). Is there any specification that you base your statements on?
Please if you are not agree, reopen it. *** This bug has been marked as a duplicate of bug 114482 ***
It's not necessary a private message, please comment here, and as I have commented, if you are not agree reopen it.
I DON'T AGREE ABOUT "RESOLVED DUPLICATE of bug 114482". I re-opened another bug 117289. I better explain the bug (I added also an attachment). Please look at it. jbfaure@libreoffice.org closed it as INVALID. He told me not re-open another bug, but to add a comment on my first bug (#114482). If this bug will not be adjusted, I will re-open another new bug. LIBRE must correct a very stupid error (it must decrease, after the setting, the iterations number by 1). Best regards Carlo Sarti P.S. I sent a mail to jbfaure@libreoffice.org, but I didn't receive an answer.
I repeat my question again: based on what do you make statement that, setting a single iteration and a huge minimum change, a value in B1 with a formula =A1=B1 must be equal to 100 when you first entered 100 into A1, and 100 after that when you have entered the 0 into A1? Please give a reason for that, other than "I want it" or "It works that way in another software, but that another software doesn't provide any specifications on that". I ask because I believe that you *misuse* the feature. The feature is created to calculate *converging* formulas that can only be solved iteratively. Software may optimize that use case, using some initial guessing values, to try to speed up or improve convergence the intended use case for some real-life scenarios. And different softwares might do this optimization differently, as soon as the end result *in the intended use case* is correct. Of course, Calc experts may have a different opinion here, and correct me. For that reason, I add Eike Rathke to the CC.
(In reply to Mike Kaganski from comment #5) > ... formula =A1=B1 ... Of course, I meant formula =A1+B1
Mike Kaganski, I think you don't understand what I write. 1) I write "please, look at my bug #117279, closed by JB Faure, where I better explained the problem (with an attached file). 2) If it is possible set the iterations number at 1, why I can't do it? Setting it to 1 means to do 1 loop (it isn't possible to set it at 0). If you do two loop, this is an error. Using the suggested value (100), to do 100 or 101 is not important. If necessary, I can copy my bug #117279. I do not agree about bug 117049 was marked as duplicate of 114482 (referred e:523). It is impossible to speak about e:523 (convergence problems) if I have not this message. The default setting are related to make many iterations to reduce the error smaller than a prefixxed value. I want make "AN ONLY" iterations. For this, the considerations done by Mike Kaganski are not valid. I put below the steps to reproduce the problem (wrong data in calculation). I would attach a my file, but I do not see, in this page, the key "Add an attachment" enabled. I hope it is in the next one (after "Submit Bug Report"). If I will not be able to add it, you will not can to use it, but the following considerations are valid. My program calculate my economic balance. I have ATTIVO/PASSIVO (CREDIT/DEBIT) at the beginning year: I put the data with the last results of previous year. I have ATTIVO/PASSIVO, at the current date. The ATTIVO/PASSIVO are equal the initials vaiues +/- ENTRATE/USCITE (INCOMES/EXPENDITURES). The ENTRATE/USCITE (from C23 and E23 and following rows) are obtained as the couple as A1 and B1 (see below in "Steps to Reproduce"). If I use my program for commercial purposes (naturally suitable modified to satisfy the complete rules of Economic balance), I would go in prison. My program well works in Excel (that I have not, but that I tested with it) and in Softmaker (PlanMaker). From this site, I downloaded a free version for Windows an another for Linux; I received, by mail, two license numbers. In Excel and in PlanMaker I have correct results; with LibreOffice (Calc Speadsheet) I have wrong results. Steps to Reproduce: 1. Open LibreOffice. 2. Open Calc Spredsheet (main page). 3. Click on Tools to open the drop down menu. 4. Select Options. 5. Open LibreOffice Calc 6. Open Calculate. 7. Check Iterative References 8. Set Steps (first parameter in Iterations) at 1 (minimum value). 9. Set Minimun Change (second parameter in Iterations) at 1000000 (a very high value, this is MANDATORY not to have e:523). 10. Click on OK. 11. Return to Calc Spredsheet (10th step). 12. Put 0 in A1 and B1. 13. From B1, write in the Insertion cell (referred to B1) =A1+B1. 14. If you obtain the message #VALUE!, you can use my trick: repeat 3td ÷ 11th step, adding some clicks on conditions for General Calculations (the message #VALUE! can be considered a little bug), instead of doing change for 8th and 9th step. 15. With 0 in A1 and B1, put 100 in A1, then ENTER; then put 0 in A1 and finally ENTER. Actual Results: The result in B1 (wrong) is B1=A1+B1=100*2+0=200 (per first input) and B1=0*2+200=200 (for the second input). Expected Results: The result in B1 must be B1=A1+B1=100+0=100 (per first input) and B1=0+100=100 (for the second input). A value in A1 is added/subtracted (depending on positive/negative value) in B1 (value updated). You are ready for a new other input. Reproducible: Always User Profile Reset: No Additional Info:
Mike Kaganski, I want to add another comment. You reproduced the described behavior, also you are not sure if it is a bug. I am sure. If I set the iterations number at 1 and I write B1=A1+B1, what does it mean? It means I want to do 1 loop (Iterations number) to calcolate the function B1=A1+B1. Now, if I have A1=100 and B1=0 (initial setting), I have B1=A1+B1=100+0=100 (correct value), but I do another loop (2 iterations), I have B1=A1+B1=100+100=200 (wrong result). I know that circular references are used to reduce the error "UNDER" (not closer) the Minimum Change and, for this, the Iterations number is set high, but I use the circular references to do a continued addition. Look at the piggy bank. If, today, you put inside 10$, tomorrow 5 and 15$ and day after tomorrow 10$, how money have you in the piggy bank? You can calcolate it with the technique previous described. The reset the data at 0 is not necessary. It needs in my file. If you don't reset the value, when you enter a value in another raw, you have an addition for two value. Have I invented a new technique? Must I patent it? I don't think. Best regards Carlo Sarti
Is there someone that exams this bug reproduced by Mike Kaganski? Does jbfaure@libreoffice.org do something? Does m.a.riosv (miguelangelrv@libreoffice.org) do something? I changed Status after adding info as required by previous status (needinfo). The system suggested me to do so. Best regards Carlo Sarti P.S. I hope that in Ubuntu 18.04, this bug is corrected.
Please answer the question in comment #1 and comment #5 Status set to NEEDINFO, please set it back to UNCONFIRMED once requested informations are provided. Best regards. JBF
I don't understand the status change done by jbfaure@libreoffice.org and miguelangelrv@liber.org. Your comment #9 is following to mine (#7 and 8). mikekaganski@hotmail.com wrote, in comment #1, "I do reproduce the described behavior". m.a.riosv, you made a "crap" to change my bug as "RESOLVED DUPLICATE of bug 114482". Please, stop to do other changes. Mike Kaganski wrote , in comment #1, "but I can't confirm the bug because I don't know if it's actually a bug … Calc experts may have a different opinion here, and correct me. For that reason, I add Eike Rathke to the CC". Eike Rathke, are you an Calc expert? have you done anything? If you are not sure if the described behavior is a bug, you mast change work. The agricolture offers many opportunities. In any case, all you must read my comments #7 and 8. I explain because it is a bug. I explain, mathematically speaking, what means to do B1=B1+A1 (one time), starting from B1=0 and A1=100. Furthermore, I add the use for piggy bank, as use of circular references with Steps (first parameter in Iterations) set to 1. Excuse me, if I, at the end of comment #8, joked writing "Have I invented a new technique? Must I patent it? I don't think", for the use as continued addition. After having added new info (comments #7 and 8), I changed the status from NEEDINFO to UNCONFIRMED, as suggested by the system. I will change again the Status to UNCONFIRMED. Who has to make a decision to solve this stupid bug? I thing that UBUNTU must replace Libre with SoftMaker. In these days, this society released "free office 2018". Best regards Carlo Sarti
I my comment #7, I added an attached: Conto.xls. Do you see it? If you don't, I can add again. Best regards Carlo Sarti
(In reply to sarti_carlo@yahoo.com from comment #11) > Eike Rathke, are you an Calc expert? have you done anything? > If you are not sure if the described behavior is a bug, you mast change > work. The agricolture offers many opportunities. Do you want to effing annoy me? Go on, goal reached.
I will ask one more time, trying to rephrase my question, since the rude behaviour of OP shows not only that s/he is not well-mannered person, but also that s/he cannot understand the idea behind the prior questions, in the hope that OP would choose to return to a constructive dialogue. Your idea is based on assumption that the initial value of B1 in the iterative calculation must be zero. Only then the single iteration of formula "=B1+A1" might bring the 100. But the actual value in the B1 at the moment of the first calculation is not 0, but (a formula) "=B1+A1", which is simply *uncertainty* initially, since it's a self-referencing formula. There's *no* number in the universe that may represent that uncertainty; and any initial number that we might choose to use as its initial value is just a guess, which will be improved iteratively. If we happen to blindly guess a value that would happen to be the limit of the iterative calculations, then the very first iteration would give a delta that is less than our minimum change, and thus, the iterative calculation will have the minimal length. Just to make things clear: the value in the B2 in the course of iterative calculations is not a single static value, but a series of values, which are expected to converge to prescribed maximum error in the given number of iterations. Let's put the series in a hypothetical B1 here in some abstract series for some unspecified formulas: > i0 i1 i2 i3 i4 ... > 0 -10 -100 -1000 -10000 ... > 1 1.1 1.11 1.111 1.1111 ... Here you may see that some formula might give a diverging results if the initial guess is 0, but converging result when the initial guess is 1. And if the initial guess would magically happen to be 1.1111111... from the very beginning, then the following iterations would not change the value in that B1, i.e., the initial guess would be the final value. Of course, it's unrealistic to expect that we would be able to guess the correct final value in that cell each time; but *generally*, the initial guess does affect the number of iterations before the delta becomes small enough; and in some cases, it even affects the very possibility to come to a converging iterations. And I repeat once more: the very idea of the iterative calculations is to make sure we come to a converging result series, not to misuse the feature in obscure ways. And again: the initial value in the cell with iterative calculations is not something that necessarily must be decided once for all; it's just an initial guess of the final result, and the quality of the guess affects the quality (time to get, and ability to get close enough to, the theoretical limit of the infinite calculation series)! There exist different approaches to guessing the initial value that starts the series; of course, *one of them* (the dumbest one) is just to take it to be zero. But this dumbest initial guess is by no means the best out there in existence. =================================================== That is why I question the validity of your assumption that it must be like that. And that is why I ask you to provide an evidence in form of documentation that states that *in the beginning of the series of iterative self-referencing calculations, the initial guess of the value in that cell must be equal to zero*! =================================================== I set the status to NEEDINFO once again. The status here is not for you, Carlo Sarti; it's for us, who decide what to do next with the issue: if we need to proceed and declare it confirmed (and thus a candidate to be fixed eventually), or if we need more details on this; or if an issue should be closed. We ask you the necessary information, because it's you who is interested in "fixing" this, so it's in your interests to provide as much information as it's required. Doing this does not mean any hostile behaviour towards you; if someone wanted, one could just close this, and not ask you in the hope that you could make the issue clear (and so, we tell you that we hope that you can convince us!). Please only change the status back to UNCONFIRMED when you have provided the requested evidence in form of references to documentation. Thank you.
Mike, I know what the OP is trying here, and this kind of self-adder in Excel works like a counter, in this example adding A1 to the current value of B1 in B1 for each iteration. An initial value of 0 of the so far uncalculated formula result in B1 is a fair assumption (the actual cell content previously in B1 before entering the formula doesn't matter at all) and seems to be what Excel uses. While detecting the circular reference we execute the underlying calculation twice in this example, hence get 200 instead of 100.
(In reply to Eike Rathke from comment #15) Great! Thanks Eike - I knew I need to ask your advise! :-)
Thanks Eike Rathke. Excuse me, I was very angry. I could not accept to meet several problems for my first bug. I made a mistake, for inexperience, opening a new bug after m.a.riosv marked it as a duplicate of bug 114482. For me, the problem was clear and had to be solved asap. Best regards Carlo Sarti
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=678f8ab7eb90f5b0dd99328957b21d11e3be68b4 Resolves: tdf#117049 do not re-interpret a single circular self-reference It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=93dcfa7ee157dd865f5d266d1f877014462eac72 Related: tdf#117049 treat yet unknown result as initial 0.0 It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending for 6-1 https://gerrit.libreoffice.org/56178 https://gerrit.libreoffice.org/56179
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8cbd64985af140b2afac6ea2fd3b9b93300f6f7c&h=libreoffice-6-1 Resolves: tdf#117049 do not re-interpret a single circular self-reference It will be available in 6.1.0.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=011a9d000e32ec55e9ef46237fc1f5a578f78f9b&h=libreoffice-6-1 Related: tdf#117049 treat yet unknown result as initial 0.0 It will be available in 6.1.0.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.