[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]

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

--- Comment #16 from b.  ---
i'm lacking an instrument to inspect *.xls files, 

saving the file as *.ods unveils one thing, 

additional to the definitions of the named ranges: 






there are additional 'definitions'? style assignments? for '_Items' and
'_Rows': 


   Named Range _Items =
$Login.$B$24:$B33
   
  
   



   Named Range _Rows = $Login.$B37


in the file, observe that they are defined without the second $-absolute-marker
... 

i - assume - there's something with theese definitions affecting the correct
interpretation of the named references as soon as you change their range
address to the same ... 

maybe someone with more knowledge about the file and data structures can shed
some light?

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

--- Comment #15 from b.  ---
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.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

--- Comment #14 from QA Administrators  ---
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today,
LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed
bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this
bug report. During that time, it's possible that the bug has been fixed, or the
details of the problem have changed. We'd really appreciate your help in
getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice
from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information
from Help - About LibreOffice.

If the bug is NOT present, please set the bug's Status field to
RESOLVED-WORKSFORME and leave a comment that includes the information from Help
- About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular
meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a
REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your
bug pertains to a feature added after 3.3) from
http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat:
https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

--- Comment #13 from Howard Brown  ---
Sorry, I don't understand how your example applies to what I'm doing.

Have you looked at the video I uploaded?  It clearly shows the behaviour that I
described in my other comments in this topic.

I created four different named ranges, where the first one is a range of values
over multiple rows, which needs to grow when new lines are inserted anywhere in
it's range.  The other named ranges use the first one to calculate a value and
need to move as new lines are added to or deleted from the first range.

I then position the active cell somewhere in the spreadsheet, but I do not
insert any lines nor do I delete any rows.  Next I open the range manager to
look at the defined named ranges and I often find that the defined ranges have
changed to different cell ranges that have no relationship with the data my
calculations are supposed to work on.

How can this be useful.  It invalidates the calculations based on the named
ranges and the results are wrong.  To me the proof that something is wrong is
that if I do the same thing using explicit relative cell references they don't
randomly change.  Also, this doesn't happen when I use Excel.

Thank you,
Howard Brown

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

m.a.riosv  changed:

   What|Removed |Added

 Status|UNCONFIRMED |NEW
 Ever confirmed|0   |1

--- Comment #12 from m.a.riosv  ---
You have the explanation in commen#9, I try to explain again.

New spreadsheet
Go to B10
Create a new named range relative to 'A1'
Go to B12
Go to edit named ranges
Now the reference is 'A3', what it's right, two cells bellow the cell where you
were when the named range was created.
Go to C10
Go to edit named ranges
Now the reference is 'B1', right again, one cell to the right of cell where you
were when the named range was created.

This means that relative references in named ranges are always relatives to the
cell 'where you are' when the named range is created.

So it's needed to be cautious on how to use relative address in named ranges,
they are specially useful to create named formulas, that you can use in
different places.

Please again, let the status as NEW.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

--- Comment #11 from Howard Brown  ---
Oh, the relative range I describe would also change if new lines were added
before the range, too, but the beginning row would be fixed at $B$24, I believe
that only the range's end would move down.  However, in my spreadsheet lines
would only be added between the beginning and end of the range, so having the
range expand is exactly what I'm looking for.  The issue is that Calce is
changing the named range even though I'm not adding new rows and often the
range reported in the range manager appears to be wrong, as is the case where
the ROWS function with the parameter $B$24:$B35 having a result other than 10. 
Sometimes it's less than 10 and others it's greater than 10, but the range
still shows $B$24:$B35.  Other times the range shows an entirely different
range, but in all cases I didn't add or remove any lines in or above the named
range area of the spreadsheet that I created the named range with.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

Howard Brown  changed:

   What|Removed |Added

 Status|NEW |UNCONFIRMED
 Ever confirmed|1   |0

--- Comment #10 from Howard Brown  ---
> What you comment as fixed references are denominated as absolute reference
> and not fixed are relative references.

Thank you, I wasn't sure what to call the types of cell references that I was
using, so I used a descriptive term in quotes to try to make the actions as
clear as I could.  By the way, when you use a $ before the column, but not
before the row, is that still a relative reference or is it called something
else?

Anyway, as I understand it a relative reference range like I'm using, i.e.,
$B$24:$B33 should remain unchanged unless I insert new rows between rows 24 and
33, but this isn't happening, as demonstrated in the video I uploaded.

I misunderstood what was causing the Err:522 errors when I first reported it,
thinking that the problem had to do with the named range begins used in the
numerator of the percent completion formula and the spreadsheet conversion to
the excel .xls format, but as I worked with the problem further, I found that
other formulas were 'outputting' Err:522 messages, and that the spreadsheet
could be saved as native .ods files or not even safed.

> Why do you have changed the status?

My mistake, I thought I was supposed to do that.  Mainly, I was trying to
respond to the assertion that the problem I was reporting was cause by my
creating the named range with circular references, but that's just a symptom of
the real problem, which is that Calc was incorrectly and randomly changing the
named ranges I created using relative cell references, and sometimes the
results are circular references.

While I didn't at first know the actual cause of the problem and I didn't use
the right terms in my description of the problem, the issue is has been the
same all along.  I'm not reporting new problems, only a more complete
description of what is happening and I think it is a pretty big issue for Calc
when you can't trust that a named range won't be corrupted, so I reported it.

Thank you,
Howard Brown

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

m.a.riosv  changed:

   What|Removed |Added

 Status|UNCONFIRMED |NEW
 Ever confirmed|0   |1

--- Comment #9 from m.a.riosv  ---
What you comment as fixed references are denominated as absolute reference and
not fixed are relative references.

Creating a new named range with a relative reference, means it's a relative
reference in relation with the cell where you are when the named range is
created.

If you are in C10 and create a new named range relative to A1, when you copy
C10 to C11 the named range reference to A2 in C11.

Please, why do you have changed the status?, if you find a new bug, do a new
report. Otherwise it's only noise, that's make difficult getting a dev taking
it.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

Howard Brown  changed:

   What|Removed |Added

 Status|NEW |UNCONFIRMED
 Ever confirmed|1   |0

--- Comment #8 from Howard Brown  ---
The video is too big to save uncompressed, but if someone wants it in another
format I can mail it directly to you.

Thanks,
Howard Brown

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

Howard Brown  changed:

   What|Removed |Added

 Attachment #125191|0   |1
is obsolete||
 Attachment #125200|0   |1
is obsolete||

--- Comment #7 from Howard Brown  ---
Created attachment 125229
  --> https://bugs.documentfoundation.org/attachment.cgi?id=125229=edit
Video showing the named ranges changing all by themselves. WinZip max
compression format.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

--- Comment #6 from Howard Brown  ---
The comment I entered with the last version of the spreadsheet was lost.  Here
it is again.

The circular reference was created by Calc, not me.  I entered the correct cell
references in the ranges I created, but then Calc changed them apparently
randomly.  I know this because after I saw that the calculations weren't
working properly, I opened the range manager and saw that the named ranges
would often have the wrong cell references.  This problem is not associated
with saving the spreadsheet as in the .xls format.  It happens in the native
.ods format, too.  Actually, the spreadsheet doesn't even need to be saved at
all, this problem often occurs just by clicking on different cells in the sheet
and then opening the range manager.

What I do know is using 'fixed' cell references, i.e., $B$24 and $B$24:$B$33
works properly, but changing the cell references to 'non-fixed' cell
references, i.e., B24, $B24, and $B$24:$B33, so that new data can be
automatically accommodated by the formulas without needing to edit the cell
ranges, causes Calc to change the cell ranges, even if no new rows are added
above the calculations using the named ranges.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

--- Comment #5 from Howard Brown  ---
Created attachment 125228
  --> https://bugs.documentfoundation.org/attachment.cgi?id=125228=edit
New .xls spreadsheet

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[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]

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

m.a.riosv  changed:

   What|Removed |Added

Summary|Formulas with circular  |In formulas with circular
   |references are not always   |references are not always
   |showed as Err522 with all   |showed the Error 522 with
   |calc functions. |all calc functions.
   |(Deactivated Iterations |(Deactivated Iterations
   |option) |option)

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs