Bug 99956 - In formulas with circular references are not always showed the Error 522 with all calc functions. (Deactivated Iterations option)
Summary: In formulas with circular references are not always showed the Error 522 with...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
 
Reported: 2016-05-19 21:09 UTC by Howard Brown
Modified: 2022-05-23 03:37 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreedsheet showing a simple example of the problem (31.50 KB, application/x-ole-storage)
2016-05-20 15:08 UTC, Howard Brown
Details
Minimal sample file (13.25 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-05-20 23:05 UTC, m_a_riosv
Details
New .xls spreadsheet (15.00 KB, application/x-ole-storage)
2016-05-22 14:54 UTC, Howard Brown
Details
Video showing the named ranges changing all by themselves. WinZip max compression format. (5.02 MB, application/zip)
2016-05-22 15:13 UTC, Howard Brown
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Howard Brown 2016-05-19 21:09:16 UTC
I created a spreadsheet consisting of a several named ranges that calculates the percent of completion using the formula:

 = 1 - Home_Page_Issues / Home_Pages_Rows

However, instead of a result, the cell shows Err:522.

Home_Page_Issues is a named range for a cell that calculates the number of issues using the formula:

 = Home_Page_Rows - Home_Page_Non_Issues


Home_Page_Rows is a named range for a cell that determines the number of rows in the range using the formula:

 = ROWS( Home_Page_Items )


Home_Page_Non_Issues is a named range for a cell that contains the formula:

 = COUNTBLANK( Home_Page_Items ) + COUNTIF( Home_Page_Items, "Yes" )

Home_Page_Items is a named range for the cells $B$3:$B102.

The Err:522 problem seems to be either that the percent complete calculation contains too many nested ranges or the division of one named range by another named range doesn't work, because if I change the formula as follows, it works properly:

 = 1 - B104 / Home_Pages_Rows

Where B014 is a cell that calculates the number of issues, and its formula still contains the two named ranges shown above for the Home_Page_Issues:

 = Home_Page_Rows - Home_Page_Non_Issues

I know that the Home_Page_Issues named range is correct because if I use it in another cell's format to show the named range's value, it shows the correct value:

 = Home_Page_Issues

And if I use that cell's address in the numerator of the percent of completion the result shows properly.
Comment 1 m_a_riosv 2016-05-19 21:45:09 UTC
Could be easy to test if you attach a sample file with the issue.
Comment 2 Howard Brown 2016-05-20 15:08:45 UTC
Created attachment 125191 [details]
Spreedsheet showing a simple example of the problem

While creating the example spreedsheet, I realized that the the problem I submitted has to do with the ranges being corrupted when I saved the spreedsheet to Excel (xls) format and reloaded it back into Calc.  When I created the Named Ranges and formulas that used the Named Ranges, everything looked good.  There is a warning about compatibility between the two file formats, but named ranges shouldn't be a problem, but since the Named Ranges on the Login and Manage Pages sheets get corrupted, it is clear that Calc isn't able to save/load the original ranges correctly.  This may or may not be the source of the Err:522 errors, but it is clear that converting the spreadsheet to the Excel xls format isn't reliable.

My issue is that no one else in my project is using LibreOffice, so I have to convert my ODS spreedsheets to Excel, which has proven to be buggy.  Fortunately, if I just use the explicit cell ranges, i.e., $B$3:#B12, then the conversion to Excel works properly.
Comment 3 Howard Brown 2016-05-20 15:10:30 UTC
Oh, and thank you for responding so quickly!
Comment 4 m_a_riosv 2016-05-20 23:05:29 UTC
Created attachment 125200 [details]
Minimal sample file

Hi Howard,

the error is right, because cell 'Login Page'.B35 is calculating the rows on their own column what implies a circular reference Err-522.
Login_Page_Items =$'Login Page'.$B$24:$B65558

Try to avoid cells with ranges including themselves.

The issue here is know why the error it's not always showed.
Seems as the error is not propagated through all functions, like SUM() does.
Comment 5 Howard Brown 2016-05-22 14:54:48 UTC
Created attachment 125228 [details]
New .xls spreadsheet
Comment 6 Howard Brown 2016-05-22 15:05:00 UTC
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.
Comment 7 Howard Brown 2016-05-22 15:13:51 UTC
Created attachment 125229 [details]
Video showing the named ranges changing all by themselves. WinZip max compression format.
Comment 8 Howard Brown 2016-05-22 15:17:12 UTC
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
Comment 9 m_a_riosv 2016-05-22 21:56:07 UTC
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.
Comment 10 Howard Brown 2016-05-22 22:31:09 UTC
> 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
Comment 11 Howard Brown 2016-05-22 22:41:07 UTC
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.
Comment 12 m_a_riosv 2016-05-23 01:53:00 UTC
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.
Comment 13 Howard Brown 2016-05-23 03:22:56 UTC
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
Comment 14 QA Administrators 2018-07-12 02:43:55 UTC Comment hidden (obsolete)
Comment 15 b. 2019-12-16 07:38:54 UTC
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:
Comment 16 b. 2020-05-22 22:13:03 UTC
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: 

<table:named-range table:name="_Issues" table:base-cell-address="$Login.$A$1" table:cell-range-address="$Login.$B$35"/>
<table:named-range table:name="_Items" table:base-cell-address="$Login.$A$1" table:cell-range-address="$Login.$B$24:.$B$33"/>
<table:named-range table:name="_Non_Issues" table:base-cell-address="$Login.$A$1" table:cell-range-address="$Login.$B$36"/>
<table:named-range table:name="_Rows" table:base-cell-address="$Login.$A$1" table:cell-range-address="$Login.$B$37"/>

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

<text:p text:style-name="P1">
   <text:span text:style-name="T1">Named Range _Items = $Login.$B$24:$B33</text:span>
   <text:span text:style-name="T1">
      <text:tab/>
   </text:span>
</text:p>

<text:p text:style-name="P1">
   <text:span text:style-name="T1">Named Range _Rows = $Login.$B37
</text:span></text:p>

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?
Comment 17 QA Administrators 2022-05-23 03:37:48 UTC
Dear Howard Brown,

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 https://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://web.libera.chat/?settings=#libreoffice-qa

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

Warm Regards,
QA Team

MassPing-UntouchedBug