https://bugs.documentfoundation.org/show_bug.cgi?id=174111
Bug ID: 174111
Summary: Calc: iterative circular reference gives wrong, stale
results when the loop splits into two branches
Product: LibreOffice
Version: 24.2.5.2 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Created attachment 209275
--> https://bugs.documentfoundation.org/attachment.cgi?id=209275&action=edit
calc spreadsheet containing minimal example
With Iterations enabled, a circular reference whose loop splits into two
branches that later merge does not converge to the correct value after an input
change. Some cells inside the loop keep stale values from the previous input.
The normal automatic recalculation (AutoCalculate) returns a value that is not
a solution of the equation.
F9 does not change it.
Ctrl+Shift+F9 (Recalculate Hard) moves the value towards the solution. It
sometimes needs several presses to get there.
A mathematically identical loop with a single branch is always correct. MS
Excel computes the same spreadsheet correctly.
Minimal example
Both rows solve the same equation N = 10 + 0.03·Input/N. Row 2 splits 0.03 into
0.01 + 0.02:
A (Input) B (q) C (branch 1) D (branch 2) E (N, iterated) F
(exact, no loop) G (error)
2 1000 =A2/E2 =0.01*B2 =0.02*B2 =10+C2+D2
=5+SQRT(25+0.03*A2) =E2-F2
3 =A2 =A3/E3 =0.03*B3 0 =10+C3+D3
=5+SQRT(25+0.03*A3) =E3-F3
Iteration settings (Tools > Options > LibreOffice Calc > Calculate): Iterations
ON, Steps 100, Minimum change 0.0001. These are saved in the attached file.
Multi-threaded calculation is OFF.
Steps to reproduce
Open the attachment and press Ctrl+Shift+F9. E2 = E3 = F2 = 12.4162, and G2 and
G3 are about 0.
Type 5000 in A2 and press Enter.
Type 1000 in A2 and press Enter, which brings back the original input.
Press F9, then Ctrl+Shift+F9 a few times.
Actual results (observed in the GUI)
State E2 (two branches) E3 (one branch) Exact
A2 = 5000 14.954 (−18%) 18.2287 18.2288
A2 = 5000, after Ctrl+Shift+F9 19.280 (+5.8%) 18.2288 18.2288
back to A2 = 1000 15.819 (+27%) 12.4162 12.4162
+ F9 15.819 (unchanged) 12.4162 12.4162
+ Ctrl+Shift+F9 12.091 12.4162 12.4162
+ Ctrl+Shift+F9 12.457 12.4162 12.4162
The stale value is visible inside the loop itself. After step 3, row 2 shows B2
= 63.216 and C2 = 0.632 = 0.01·B2 (consistent). But it shows D2 = 5.187, while
=0.02*B2 should give 1.264. D2 was not recalculated even though its only
precedent, B2, changed.
The same happens through the API: setValue() on A2 followed by getValue(),
calculate() or calculateAll(). A test with ranges in the loop (for example
SUM(I1:J2) or VLOOKUP over a block that contains loop cells) gives even larger
errors (about 11%).
Expected results
The same input must give the same converged result. E2 should equal E3 and F2,
with G2 ≈ 0 within the "Minimum change" tolerance, after each input change and
without needing Ctrl+Shift+F9. That is how MS Excel behaves.
Impact
Any model with an implicit equation that branches is silently wrong in Calc. An
example is a heat exchanger, where the heat-transfer coefficients depend on the
heat flux, which in turn depends on the number of tubes.
The problem is worse with Tools > Solver (NLPSolver DEPS/SCO/Swarm). The Solver
changes the variable cells and reads the objective through the normal
recalculation, so each candidate is evaluated partly with the previous
candidate's values. In a real model this gave objective values up to 0.8% to
24% off, depending on the combination. Optimizer results were wrong or not
reproducible, and the calculation chain occasionally broke into #DIV/0! with no
recovery other than reloading the file.
--
You are receiving this mail because:
You are the assignee for the bug.
