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