[Libreoffice-bugs] [Bug 99956] In formulas with circular references are not always showed the Error 522 with all calc functions. (Deactivated Iterations option)

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=99956

--- Comment #15 from b. <newbie-02 at gmx.de> ---
hi, 

just trying to analyse and writing to train my experience ... 

in the sample from comment #5 i see only one problem, cells B15 and B16 from
the 'explicit cell ranges' area reference to range ($B$24:$B33) from the 'named
ranges' area, thus producing wrong results. changing the references to
($B$3:$B$12) corrects that and holds, even on insertion of new rows. 

'relative named ranges' are funny, are new to me and may explain plenty
misunderstandings ... 

in the old - deleted - sample from comment #2 i see err:522 on load in cells
B34 and B35 of the sheet 'Login Page', 

on 'thinking through the steps' i see 'mixed' references for the named ranges
'Login_Page_Items' and 'Manage_Page_Items' with 'fixed' column 'B', fixed start
row, but 'unfixed' end row some 65xxx rows below the !actual cell! (the
'focus'), probably that's not what the OP wanted. to keep in sync with his
'fixed ranges'-sample the references should end at $B$33 resp. $B$39, that
would: 
- give correct compatible results, 
- also 'expand' on insertion of rows into the referenced range, 
- stop the calculating cells B34:B37 on 'login' and B40:B43 on 'manage' sheet
from referencing themselfes, and thus make everything easier, 

with the formulas and dependencies given in the sample, cell: 

B37 calculates 65548 rows - including itself, but only counting, not
referencing for calculation -, that works, 

B36 calculates 65542 'non-issues' - including itself, and that's funny because
normally it couldn't know if itself wouldn't result to 'blank' or 'yes' and
thus has to be deducted from the result ... but without iterations it's either
'blank|yes' or not, in both cases the result of counting and calculation is a
number, and that's not 'blank|yes', thus a distinct result is possible ... 

B34 and B35 show err:522, but that's in any way stored with the file, on
changes in the referenced area both change to show values, even without
iterations, 

there are! circular dependencies in the sheet, B36 references itself, probably
that's not 'seen' by the resolver because it's nested in the countif function
and by 'indirect' referencing through the name? 

B34 and B35 depend on B36, and that circular on their own results, that's
somehow a 'second-level-circular-dependency'? one can 'solve' this by
activating 'tools-options-libreoffice calc-calculate-iterations' and setting it
to at least 2, 

the values calculated for issues and non-issues are wrong (functional,
mathematical they are correct), as the 'result-cells' are included in the
calculated range and not correctly excluded from being an issue as they are
neither blank nor 'yes', 

and - further incorrect - as =COUNTBLANK(Login_Page_Items) produces another
value than =ROWS(Login_Page_Items) reg. the different interpretation of
'Login_Page_Items' relative to the cell with the formula, 

correcting the variable and 'circularity-producing' ranges to end at $B$33
resp. $B$39 solves all misbehaviour of the sample, 

issue left: is it correct for countif to calculate results for circular
dependencies without iterations enabled ... ??? 

in a way countif is not 'circularity aware', try: 

A1: 1
A2: 1
A3: =COUNTIF(A1:A4;1)
A4: =COUNTIF(A1:A4;2)

shows '0' here for A4 without iterations: wrong as A3 results to 2, 

shows err:523 here with iterations activated, 

shows '0' again if you allow 'minimum change' to be 1  

stopped experimenting here ... 

original problem: wfm, 

countif circularity behaviour: open new bug? 

(may be there is a binary fault in the code for the decision error/result, but
may be as well i'm short in understanding circularity, iterations and so on) 

all tests with: 

Version: 6.2.8.2 (x64)
Build ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc:

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20191216/d183a4e8/attachment-0001.htm>