Description: Goal Seek does not generate expected results in specific mathematical function when target value is set to zero 0 but generates expected results when the target value is set to 0.0001. The function includes SQRT in denominator. In Microsoft Excel the same file works fine and Goal Seek to zero generates the expected result as -0.00000583...... Steps to Reproduce: 1.In existing Excel file, opened through LibreOffice, the following exist: D5 = 0.6 D6 = 650 D7 = 4*0.4/(PI()*D5^2) D8 = D7*D5/0.00000101 D9 = 0.013647 D10 = 1/SQRT(D9)+2*LOG(2.51/(D8*SQRT(D9))+0.06/(D5*1000*3.71)) D11 = D9*D6*D7^2/(D5*2*9.81) 2. The generated value of D10 can be seen to be equal to 0.0007979.... 3.Run Goal Seek with FORMULA CELL D10, VARIABLE CELL D9 AND TARGET VALUE zero, so 0 Actual Results: - New value of D9 generated by Goal Seek is 0.01364942...... - Generated value in Formula Cell D10 is 1.055410...... Generated value is not a close approximation to zero. Expected Results: - The generated value in Formula Cell D10 should be zero or an approximation to zero - The generated value in Variable Cell D9 should produce the correctly expected result to make Formula Cell D10 equal to zero or approximately close to zero Reproducible: Always User Profile Reset: Yes Additional Info: - Version LibreOffice_7.3.3_MacOS_x86-64 (Bug reporting dropdown menu did not accept my 7.3.3.2 choice, so I picked another one from the list) - Bug occurred also in previous version but I have not noted which one it was because I reinstalled to have the latest version (possibly it was 7.2) - Reproduced the bug many times - Tried with target value 0.0001 instead of 0 and the result is correct, Goal Seek works in this case (it generates a value that is a close approximation to target value 0.0001) - Tried with a simple function F(X) = A*X+B and target value 0 and the result is correct (generated value equal to zero) - Tried with the same simple function and target value 0.0001 and the result is correct (generated value close approximation to target value 0.0001) - File has been used multiple times with Excel goal seek and it works with target value 0 in Excel generating the value D10 = -0.00000583...... - Tried in Safe mode, problem persists but calculations are slightly different - Don't know if OpenGL is enabled or not QA/Bugzilla/Components/Spreadsheet/Help Calc FORMCONTROLS First time reporting a bug, not an IT person, hope it makes sense, thanks
Thank you for the report. Please attach here the Excel file
Created attachment 180436 [details] Excel file containing bug replication try-outs File originally created in Excel; used and adapted to new values (formula untouched; only number values have changed) within Libre Office
[Automated Action] NeedInfo-To-Unconfirmed
Already in 3.5.0 Arch Linux 64-bit, X11 Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: e78859005b632860c9464335fdf6836c4fd41807 CPU threads: 8; OS: Linux 6.1; UI render: default; VCL: kf5 (cairo+xcb) Locale: fi-FI (fi_FI.UTF-8); UI: en-US Calc: threaded Jumbo Built on 12 January 2023
Maybe the easiest example (inspired by bug 161338): A1: 200 A2: =1/A1 Goal Seek Formula cell: $A$2 Target value: 100 Variable cell: $A$1 The method used in ScDocument::Solver (sc/source/core/data/documen4.cxx), Regula Falsi, is known to have limitations for discontinuous functions (like the cases mentioned here). Some improvements are needed to overcome the limitations. Excel's Goal Seek converges much slower in these cases, compared to instant failure of Calc; it looks OK to try to introduce additional steps (alternative methods? more clever detection of special points?) here, if that helps.
But in fact, the original report is wrong. The step 3 (Run Goal Seek), putting the value of 0.0136494244938458 into D9, produces not "1.055410......" in D10, but specifically 1.05498026314876E-07 - i.e., approximately 1*10^-7 - which *is* a good approximation of the target value of zero (its usual representation is 0.00000010549802631488; at least it's much better than original 0.0007979). And Excel, with its suggested variable value of 0,0136494425233952, producing target value of -5,82678759286637E-06 (-0,00000582678759286637), is in fact about 50 times *worse* (!) than the Calc's result. Let me hijack the bug for comment 5. Everything else here is INVALID.