error 522 circular reference

Discuss the spreadsheet application

error 522 circular reference

Postby toad » Wed Jan 23, 2008 5:27 pm

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.
User avatar
toad
 
Posts: 20
Joined: Fri Nov 30, 2007 3:22 pm

Re: error 522 circular reference

Postby TerryE » Wed Jan 23, 2008 5:41 pm

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.
TerryE
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: error 522 circular reference

Postby toad » Wed Jan 23, 2008 5:48 pm

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.
User avatar
toad
 
Posts: 20
Joined: Fri Nov 30, 2007 3:22 pm

Re: error 522 circular reference

Postby TerryE » Wed Jan 23, 2008 7:51 pm

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.
TerryE
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: error 522 circular reference

Postby kingfisher » Wed Jan 23, 2008 10:07 pm

It is possible to enable interation: Menu: Tools >Options >OO Calc >Calculate : Iterative References.
OpenOffice 3.3 on PCLinuxOS. There are 3 kinds of people: those who can count and those who can't.
User avatar
kingfisher
Volunteer
 
Posts: 1861
Joined: Tue Nov 20, 2007 10:53 am

Re: error 522 circular reference

Postby Villeroy » Wed Jan 23, 2008 10:24 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17320
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: error 522 circular reference

Postby TerryE » Thu Jan 24, 2008 12:28 am

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.
TerryE
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: error 522 circular reference

Postby toad » Thu Jan 24, 2008 1:26 am

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
toad
 
Posts: 20
Joined: Fri Nov 30, 2007 3:22 pm

Re: error 522 circular reference

Postby Villeroy » Thu Jan 24, 2008 1:45 am

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17320
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 24 guests