Error 522 on calc... only when closed and reopen

English
#1

Good morning, I’ve created a big file on calc (more than 10Mb) with lots of formulas and more pages.
In one of these pages there is a formula that start from bottom to top, with length of 1200 rows and 10 columns.
There is no circular reference in these rows/columns, and the formula works.
When I close this spreadsheet and reopen, in all of these column there is error 522. I delete all values except of the last one, and then copy again, the formulas works again.
I could send the files, but as I said is very big.

Thanks

#2

522 = Circular reference
Formula refers directly or indirectly to itself and the Iterations option is not set under Tools - Options - LibreOffice Calc - Calculate.

See also:
Error Codes in LibreOffice Calc

2 Likes
#3

Thanks for your reply.
Sadly, that’ not the case: the formula does NOT refer to itself, and exactly the same formula in another sheet (copy-paste), works.
Works in this sheet as well, but only when I delete it and re-copy. If I save the document, close and reopen, doesn’t work anymore, that’s the strange things.

EDIT: If I set the iteration options, the errore became 523 (not convergence)

#4

You can enter a bug in Bugzilla.

How to Report Bugs

Please post the link from the bug here. Thanks.

#5

But before that, at least show us the formula - perhaps because of its length, you don’t see the error inside. You should not upload a large book, just show the formula and describe in which cells it is located (cell addresses and sheet name) - let’s try to analyze it together.

1 Like
#6

Before doing that (because this is not a single formula, but lots of cells with different long formulas), I would ask: I found that in one of that cells, I calculate the square roots (using the sqrt built in formula) of a very big number (the order is 2.00E+07), could this create that problem?

EDIT: I changed that part and no solutions, so that big number is not the problem.
Here there is the link with that file:

The problem is in the sheet “Pomposa - calcoli”.
The sheet “Misano - calcoli” use exactly the same formulas, but there isn’t problem.
I’m sorry for the big files, but I don’t find another simple way to describe exactly the problem without send the entire file

#7

Ok, so, i delete all the data on one sheet (not the one with err.522), to reduce the size of the file.
By doing that, it works, no more err.522.
So I think the problem is the size of the file, maybe LibreOffice cannot manage big number of data

#8

Thank you for changing the file to an archive - OneDrive did not want to give the ODS.
I’m afraid that even if you publish a bug report and attach this file as an example, the bug will be fixed not very soon. To understand the reason, someone have to go through the entire chain of calculations to the source of the error, and this will take time and endurance. I started from the place you indicated, with t_giro_pomposa, which contained an error and got only to one problematic cell - $'Pomposa - Calcoli'.AT1202. There is a formula =m_tot*AQ1202^2/M1202, each of whose elements is defined =260*58.2137879763306^2/265.729248847578 However, as a result of calculations, the formula returns Err:522. I am sure that this is not the only such cell in the book, but finding them all is a very difficult task.
Perhaps your assumption is correct and there is some kind of “error count”, reaching which Calc stops calculations so as not to freeze at all, and not processed, not recalculated cells are marked as erroneous. This is just my guess, it is possible that in reality everything is more complicated (or easier). I’m sorry, but I can’t help.

#9

Even if you cannot solve this problem, thanks for your support.
I will report this issue as bug, to solve right now I’ll try to divide the file in more smaller files, so maybe in this way he can manage all these data

1 Like
#10

Hi, just a wild suggestion, have you tried doing a recalculate hard in a macro, and adding the macro to the sheet in Tools/Customise/Events, Open document. This is the macro I have used, as some totals/counts/averages were not being fully re-calculated on just opening a large sheet. The example below, opens to my first sheet, and at cell A17, and all my totals/etc have been updated.

Macro Code,

sub Main22
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:CalculateHard", "", 0, Array())
rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Nr"
args2(0).Value = 1
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args2())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$17"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
end sub

Created using Windows 10 Home, LO 7.3.0.3

#11

Thanks for your suggestion.
Sadly, I never use macro so don’t know how to use them.
I’ll try to study yours and make some tests

#12

Forget about macros. How often can you press Ctrl+Shift+Enter compared to the time and enormous effort that is required to understand macro code? This is a bug that will be fixed once it is filed with a demo document that allows reproducing the error.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.