error 522 circular reference

Discuss the spreadsheet application
Post Reply
User avatar
toad
Posts: 20
Joined: Fri Nov 30, 2007 3:22 pm

error 522 circular reference

Post by toad »

Hi,

hope somebody can help a total spreadsheet noob like me...

Problem:
I want to keep a running total in column C of amounts I input in column A, i.e. I have one input field (A1) and one running total field (C1) which gives me the total of all amounts I input in A1 over the course of time...

My solution involved entering x amount into column A and a function =(A1+C1) which gives me error 522 (recursion) :o

I have looked at help and come to the conclusion that I am attempting an iterative calculation!? but haven't got much further yet.

Any hint or advice would be much appreciated.
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: error 522 circular reference

Post by TerryE »

You can't do this for architectural reasons. A spreadsheet is a functional model and not a procedural model. That is cells are calculated as formula which transforms other cells. These dependencies for a directed graph. For the sheet to be computable then you can't have any loops in this graph, and C1 = A1 + C1 is exactly that.

If you want to keep a record of entries in A1 then you need to keep a history in say D1,E1,F1... and then you can say C1 = SUM(D1:IV1) or whatever, but then you still need to manually (or through a macro maintain that history
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
User avatar
toad
Posts: 20
Joined: Fri Nov 30, 2007 3:22 pm

Re: error 522 circular reference

Post by toad »

Thank you very much for your quick reply.
TerryE wrote:You can't do this for architectural reasons. A spreadsheet is a functional model and not a procedural model. That is cells are calculated as formula which transforms other cells. These dependencies for a directed graph. For the sheet to be computable then you can't have any loops in this graph, and C1 = A1 + C1 is exactly that.
Just to reiterate for my own understanding - you are saying that a loop is impossible...
TerryE wrote:If you want to keep a record of entries in A1 then you need to keep a history in say D1,E1,F1... and then you can say C1 = SUM(D1:IV1) or whatever, but then you still need to manually (or through a macro maintain that history
I am not interested in the history if that makes it easier.
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: error 522 circular reference

Post by TerryE »

toad wrote:Just to reiterate for my own understanding - you are saying that a loop is impossible...
Yes, that's what I am saying.

What you could do is to record or write a macro which when you run in copies a range in column A, then does a Paste Special->Values to the equivalent cells in B, and then a Paste Special->Add Values to the equivalent cells in C and then deletes the contents of A. You can then attach this to a key or a button (other posts tell you how to do this).

This should do the sort of thing that you seem to want.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: error 522 circular reference

Post by kingfisher »

It is possible to enable interation: Menu: Tools >Options >OO Calc >Calculate : Iterative References.
Apache OpenOffice 4.1.9 on Linux
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: error 522 circular reference

Post by Villeroy »

I am not interested in the history if that makes it easier.
So you are not interested in any result at all after a single typo occured.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: error 522 circular reference

Post by TerryE »

kingfisher wrote:It is possible to enable interation: Menu: Tools >Options >OO Calc >Calculate : Iterative References.
K, don't bring this one into play. It isn't what toad wants, but is a cludgy version of goal seek that doesn't work very well. What this assumes is that the cyclic relationship will converge to a limit solution. The calculation is reapplied N times or until the cell values converge within some tramline. AVOID
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
User avatar
toad
Posts: 20
Joined: Fri Nov 30, 2007 3:22 pm

Re: error 522 circular reference

Post by toad »

Thanks once again for your comprehensive reply.
TerryE wrote:What you could do is to record or write a macro which when you run in copies a range in column A, then does a Paste Special->Values to the equivalent cells in B, and then a Paste Special->Add Values to the equivalent cells in C and then deletes the contents of A. You can then attach this to a key or a button (other posts tell you how to do this).
Wow this does indeed sounds possible. Your explanation, comprehensive it might be to the initiated, means very little to me. I am quite willing to read through the forum and do "my bit" but I'm not sure which term I should search for to get the relevant bits and pieces.

As for the comment about typos - dead right! The ideal situation would be to click on the input field and be presented with an input mask where you input number after number which is added onto the total - and the record itself is stored in some obscure (i.e. invisible) part of the spreadsheet.

Is that possible?
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: error 522 circular reference

Post by Villeroy »

As for the comment about typos - dead right! The ideal situation would be to click on the input field and be presented with an input mask where you input number after number which is added onto the total - and the record itself is stored in some obscure (i.e. invisible) part of the spreadsheet.
What you really want is a database form, where you type a set of related values (a record) into a form, submit the form (store record in database) and then use the same input fields for the next record. Then you open some kind of view, query or report to see aggregations of the database (combinations, sums, averages, ...).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply