[Solved] Simple Calculation Gives Me "Err:522"

[Colin-Campbell]

SOLVED

=(G369 / 1.609344)
This is the contents of my cell. I'm just converting kilometers to miles.

=(G369/(DAYS(A369;$A$5) + 1))
This is the contents of another cell, where I'm getting kilometers per day.

One day, I'll open the spreadsheet, and the proper result will show. Another day, I open, and get Err:522. Then, I go edit the formula to add or remove a space, save it, and all is well -- until the next time that it isn't. The two cells seem to the the error at the same time.

The problem seems to have persisted across more than one update of OpenOffice. (I'm now on 4.1.3.)

Is there something I should change in these cells to keep the error from popping up?

[Zizi64]

Error code desription from the HELP:

522


Circular reference


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

[Zizi64]

Please upload your ODF type sample file here.

[Colin-Campbell]

Zizi64,
I've attached a slightly simplified copy of this year's Bicycle Log.

[Zizi64]

Where is the "Err:522" cells in your sample file? I can not see them even if i unhide the hided rows...

[RusselB]

Like Zizi, I couldn't find the error message you are reporting doesn't show anywhere in your sample file.
On a different topic, I noticed that you're working with a fixed amount for converting kilometers to miles, and, while it works, thought I'd point out that Calc does have a conversion function for conversions like this. It's called CONVERT_ADD

[Colin-Campbell]

I'm sorry, the error occurs in cells H369 and G372. I forgot to make clear which cells I was talking about. I can't make it happen. It just opens with the error showing in those cells from time to time. If I then edit the cell, and simply add a space or delete a space around the "/" or "+" operators, the error disappears and the proper result is displayed.

Since I'm not changing what I asked OO to calculate, just the format of the calculation, I'm guessing it is an obscure error in the software.

And I should say to both of you, thanks for looking into my problem!

[Zizi64]

There some strange error is your file, but i do not know what is it. The sample file opened without any problem in my LibreOffice 6.1.5 version, but it throws a strange error message opened it in my Apache OpenOffice 4.1.5 version.

The error message shows an error in the macro module named Module1, but the document has not such Module (tested in the Basic IDE). And - as I see it - there is not any active event in your document to launch a macro at the opening the file.

Try to save your data!

I suggest you copy/paste all of the the UNFORMATTED content into a new empty spreadsheet document - before it will be damaged irreversibly. You need recreate all of the the formulas in the new file when you paste the content as UNFORMATTED text from the old one.

You can try paste the data ant the formulas too into an another new file.

[RusselB]

The file opened with no problems using my version of OpenOffice 4.1.6
No error messages were found anywhere in the spreadsheet that was attached.
That said, based on the information provided, especially since there are contradictions, I have to support the recommendation of copying the unformatted data to a new file, then recreate the formulas in the new file.
Having the original file open so you can compare the formula you are entering to the original isn't a bad idea, if you can do so.

[Villeroy]

Why so complicated?

More explanations on similar topic: viewtopic.php?t=97603&p=468221#p468187

[Colin-Campbell]

Thanks to all of you for your time looking into my question! I am going to start over as you suggest.
=====
Wow, Villeroy, I'm no expert with Calc, and I can't really understand what you did with the data! But I'll try to figure it out.

As to the complexity:
I just started several years ago, adding up my rides so I could brag about how much cycling I did. Then, I got a second and a third bike, so I wanted to keep records for each one. I used to climb more, so once the Garmin cycling computers came along that showed elevation gain, I wanted to keep track of that. Tire life has been of interest, so I calculate that.

Power meters came along, and I wanted to track battery life for those.

Then I bought some Assos shorts, which are expensive, and the shorts included a statement that they could last about 10,000 km, so I started tracking that.

I left out some parts of my actual spreadsheet, where I track spending on maintenance and new bikes, accessories, or supplies.

I've never had a problem with the spreadsheet, through various Lotus, MS, and Apache tools, over about 20 years, except for this silly error message.

I'm going to do some reading about pivot tables!

[Villeroy]

https://wiki.openoffice.org/wiki/Docume ... /DataPilot ("data pilot" = "pivot table")

The precondition is a normalized table. Your table is a cross table. In a cross table you have to enter each and every value at the right crossing of row label (your dates) and column label (the bike names).

With normalized table every row describes one distinct item. In this case a a bike trip with attributes date, bike and distance. You insert one new row for one trip anywhere in the table, fill it with all required values and that's it. Notice that the pivot table is a cross table but not the table with the actual data.
I only used 3 attributes for the demo and left out the times etc. Of course you can include as many fields as needed and derive as many pivot tables as needed.

[Villeroy]

A database driven approach with very easy data input for new bike trips and one report (requires LibreOffice or the report builder extension for OpenOffice). This way you can forget all formulas once they have been created. The "formulas" in databases are queries which return full record sets rather than single result values for one row or one column. This is a little bit tricky to build but extremely easy to use once it has been built.

[stansb]

Thought I'd add this comment for anyone else who experiences the psuedo random non-circular aspect of Err:522. What worked for me was turning off the multi-threaded calculation.