https://bugs.documentfoundation.org/show_bug.cgi?id=149333
Bug ID: 149333
Summary: FORMCONTROLS Calc Spreadsheet Goal Seek calculation
error in specific formula that works in Excel
Product: LibreOffice
Version: 7.3.0.3 release
Hardware: All
OS: macOS (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: libreoffice-bugs@lists.freedesktop.org
Reporter: naci...@gmail.com
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
--
You are receiving this mail because:
You are the assignee for the bug.
