[Solved] 504 & 522 Errors... sometimes

[Amylase]

I recently just turned off the option Iterative References due to having variable answers. Now I am having variable errors such as err:504 & err:522. I look in these error cells and usually can't find anything wrong with them. This eventually clears up by reopening the document or using the undo/redo arrows. Is there something that would be causing this? Or maybe I am just not that good at doing this stuff? Help appreciated.

Most Recent 504 error:
=IF(AND(OR(INDEX(A4:AMJ4;COUNTA(A4:AMJ4)- 0 )>0.7*Data.D20;AND(AND(INDEX(A4:AMJ4;COUNTA(A4:AMJ4)-0)+INDEX(A4:AMJ4;COUNTA(A4:AMJ4)-2)>0.7*Data.D20);LEFT(INDEX(A3:AMJ3;COUNTA(A3:AMJ3)-1);8)="Late Fee";INDEX(A2:AMJ2;COUNTA(A2:AMJ2)-0)<INDEX(A2:AMJ2;COUNTA(A2:AMJ2)-2)+ IF(B11=0;44;14) ;AND(INDEX(A3:AMJ3;COUNTA(A3:AMJ3)-0)="Payment";INDEX(A3:AMJ3;COUNTA(A3:AMJ3)-2)="Payment")));OR(AND(INDEX(A3:AMJ3;COUNTA(A3:AMJ3)-0)="Payment"; IF(L12>=L13;AND(INDEX(A2:AMJ2;COUNTA(A2:AMJ2)-0)<=L13;INDEX(A2:AMJ2;COUNTA(A2:AMJ2)-0)>DATE(YEAR(L13);MONTH(L13)-1;DAY(L13)));AND(INDEX(A2:AMJ2;COUNTA(A2:AMJ2)-0)<=DATE(YEAR(L13);MONTH(L13)-1;DAY(L13));INDEX(A2:AMJ2;COUNTA(A2:AMJ2)-0)>DATE(YEAR(L13);MONTH(L13)-2;DAY(L13)))) ;LEFT(INDEX(A3:AMJ3;COUNTA(A3:AMJ3)-1);8)<>"Late Fee");AND(INDEX(A3:AMJ3;COUNTA(A3:AMJ3)-0)="Payment";IF(L12>=L13;INDEX(A2:AMJ2;COUNTA(A2:AMJ2)-0)>L13;INDEX(A2:AMJ2;COUNTA(A2:AMJ2)-0)>DATE(YEAR(L13);MONTH(L13)-1;DAY(L13)));LEFT(INDEX(A3:AMJ3;COUNTA(A3:AMJ3)-1);8)="Late Fee")));1;M11)

Saved Document and reopened it and behold it's okay... for now


Most Recent 522 error:
=IF(AND(Data.D23>Data.D21;B11=0);1;IF(AND(Data.D23>DATE(YEAR(Data.D21);MONTH(Data.D21)-1;DAY(Data.D21)+Data.H13);B11=0);1;0))

but it is currently FALSE not err:522


????? How do I get a stable spreadsheet out of this mess ?????

[Zizi64]

From the HELP:

504
Parameter list error
Function parameter is not valid, for example, text instead of a number, or a domain reference instead of cell reference.

Tip: Check your formula.

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

Tip: Do not use circular reference. Or if it is necessary, set the iteration options.


Please upload your .ods type sample file here.

[Amylase]

Oh thank you so much for your time Zizi... you seem to know all the answers

Typical places where errors show up 522 & 504 (both only sometimes depending on the date in data.D23):
Data Sheet:
E28:E30
I28:I31

Formula Sheet
B12
L11

[Amylase]

I can copy and paste an error cell contents into another cell and it displays correctly

[RoryOF]

I can copy and paste an error cell contents into another cell and it displays correctly

That, surely, is an indication that the formula is a circular reference when in its original cell.

In which cells were your original 504 and 522 errors?

[Amylase]

an indication that the formula is a circular reference

I am sorry maybe I am not explaining it correctly. That was an err:504 and I copied and pasted it and then back into its original position and it cleared up. Nothing else changed. These errors come and go by simply by saving and reopening the sheet as well. What the hell?

[Amylase]

In which cells were your original 504 and 522 errors?

504 in formula.B11
522 in data.I28:I31

The errors come and go and also change locations. It seems like the largest contributor is in formula.b11 that has also come up as 522 circular but not the last time... is was 504.

[RoryOF]

There is probably what is known in computing as "a race condition", where one set of calculations finishes at a different speed before another (caused, perhaps, by short circuit evaluation for particular values), and the resulting conditionals differ as a result. Unless there is some error obvious to someone else, you might need to restructure your spreadsheet. I see a number of repeated evaluations - I would extract these to their own column and replace them in your complex formula by a reference to their result (probably a true/false value). I would also move your result cells to the right and down, so that the calculations of the entire sheet were complete before the results were tested in your result formulae.

BUT: I'm not a Calc expert, so take my advice in that light.

[Amylase]

There is probably what is known in computing as "a race condition"

Well that is the only thing that seems to make sense. I am going to precede as if this is what is wrong. After your reply, I came across this article...http://www.decisionmodels.com/calcsecretsc.htm. I know it is not OOo but likely to be similar enough to show further clarity of the difficulties my sheet is going through. Now just to figure out how to clean this up