Bug 152385 - Circular dependency calculation fails, in Excel it's correct
Summary: Circular dependency calculation fails, in Excel it's correct
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calculate
  Show dependency tree / graph
 
Reported: 2022-12-05 19:12 UTC by soloturn
Modified: 2023-07-27 15:27 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

Attachments
circular dependency calc bug (26.36 KB, application/vnd.oasis.opendocument.spreadsheet)
2022-12-05 19:12 UTC, soloturn 		Details
correct (56.32 KB, image/png)
2022-12-13 06:19 UTC, soloturn 		Details
wrong, 812 is only once, other fields still have old value (56.26 KB, image/png)
2022-12-13 06:22 UTC, soloturn 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description soloturn 2022-12-05 19:12:04 UTC 
Created attachment 184003 [details]
circular dependency calc bug

see attached file, where percentages get subtracted. the percentages base is in the middle of the sheet, this leeds to slight circular reference. excel is able to calculate it correctly. libreoffice only if one enters the source number a couple of times.
Comment 1 m_a_riosv 2022-12-06 15:31:47 UTC 
Works fine for me, no errors.
Do you have enabled?
Menu/Tools/Options/LibreOffice Calc/Calculate/Iterative references.

Please always paste here the information about your LibreOffice, there is a button to copy it in Menu/Help/About LibreOffice.
Comment 2 soloturn 2022-12-13 06:14:58 UTC 
i enabled it and let it be default 100 / 0.001, then 1000 / 0.001. it changed the result that one needs to only 2-3 times enter it to get the correct result.

Version: 7.4.3.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 6.0; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
7.4.3-3
Calc: threaded
Comment 3 soloturn 2022-12-13 06:19:45 UTC 
Created attachment 184122 [details]
correct

initial state, correct.
Comment 4 soloturn 2022-12-13 06:22:07 UTC 
Created attachment 184123 [details]
wrong, 812 is only once, other fields still have old value
Comment 5 soloturn 2023-01-05 08:52:12 UTC 
@m.a.riosv how did you do to not get this error? which environment and which version did you use?
Comment 6 m_a_riosv 2023-01-06 14:49:15 UTC 
You don't need to enter the number several times, but Calculate [F9] 3/4 times, do it.
Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 12e8d57e791bb1befc0716d4d02af7d1d1ccb4ae
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: es-ES (es_ES); UI: en-US Calc: CL threaded Jumbo

Please, @Eike could you give us your opinion about it is a bug or not.
Excel does it in one step, Calc needs 3/4 to do it
Comment 7 Eike Rathke 2023-03-15 14:36:37 UTC 
It's a bug, but hard to solve.
Plus, Excel has a different opinion on what convergence (maximum epsilon of minimal change) in circular iterations could mean (and sometimes presents a solution that is no solution at all).
Which isn't the case in this case though..

If you try the same document in Gnumeric you'll get similar results as in Calc, the final result is only after repeating recalculation several times (hitting F9 on D8 or any other involved formula cell).
Comment 8 Kelson 2023-03-19 14:34:59 UTC 
@Eike Thank you for the confirmation!