Circutology question

[USFNick]

Not sure if anyone can help me out here but not sure where else to turn. im writing a small paper for class and its on Circutology/feedback affect and how it relates to calculating EFN. I have been all though google with no luck. I understand what circutology (which im not sure if thats the correct term or the term the teacher made up) is. Its when you have a formula that relates back to another cell with a formula that relates back to another cell and this all creates a big circle. when i run into the problem i usually set the iterations to 1 and the calculations to manual and enter all the data and then slowly manually go through them but im not sure if thats the correct way. Like i said im not sure if this is the place to ask but i figured it couldn't hurt

Thanks

[shg]

I don't understand what your question is, but they are called circular references. It occurs when a cell is in its own calculation chain.

[TMS]

The term you are looking for is "a Circular Reference".


http://office.microsoft.com/en-gb/ex...005200285.aspx


Regards, TMS

[FDibbins]

Hi, welcome to the forum

Its called a circular reference (or circ ref), and can be as simple as A1=B1 and B1=A1, or it can be from a really complex system.

Excel tries to help by showing you where it *thinks* the circ ref is, but this is often not too accurate. I always start where it says it is, andthen just delete that cell (or column), then see if it goes away. If so, then you are in luck and you have found the location - just fix that formula If the error is still there, , go to the new location and delete that cell/column. repeat until the error goes away, and you have found the location...you can then use CTRL Z to undo all your deletes and then go to work on fixing the problem

[Richard Buttrey]

Hi Nick,

Good question.

The term is actually 'circular reference' and it is exactly as you describe when a formula in one cell is ultimately dependent on itself. The simplest example is, assuming A1 is the cell in question, is
A1=A1+1

That's of course highly unlikely and no one is likely to make that mistake since it's so obvious. Usually the circular reference occurs because several cells are involved before the reference back to the original cell. e.g
A1: =B1*2 , B1: 5 , C1: =B1*4+D1, D1: =C1-A1

With iterations turned on subsequent calculations change the various numbers even though no constant entries have been changed. Depending on the calculations sometimes these might oscillate between the same numbers and at other time you'll get a convergence on a number or runaway numbers and finally end up with a very large or very small number and eventually probably an error. With iterations turned off you'll simply see the reference to the first cell that's causing the circular reference problem in the status bar at the bottom, although it's not always obvious when there are many mutually exclusive circular reference what the logic is that determines the cell indicated.

Usually circular refs are a bad thing and should be avoided, but they can be useful in situations like say financial cash flow modelling or apportionment routines when say an interest charge is dependent on the principal amount plus a previous interest charge.

The usual way to get rid of circular references is to use a sheet change macro that copies the result of a formula and pastes the result back as a constant value thus overwriting the original formula.

Hope this helps.

[MrShorty]

As noted, the official spreadsheet term is circular reference. I think the broader term or concept being discussed is what Wikipedia calls an "iterative method" http://en.wikipedia.org/wiki/Iterative_method There are several iterative methods (such as a bisection and Newton-Raphson root finding algorithms). At its simplest, the algorithm you describe is what I call "successive approximations" others call it "simple iteration." Here's a thread I started discussing a successive approximation algorithm for solving quadratic equations. http://www.excelforum.com/excel-form...ate-event.html