Circular Reference in Excel

Not many people might have seen this error message, but I used to see it quite often during the time I used to be a business analyst for financial software.

This comes when you do something like I did below:So basically, I had 1 in the first cell, the third was the sum of the two cells on the left and the fourth was the sum of the second and third.

or D3=1, E3=2, F3=D3+E3, G3=E3+F3

Finally, I made D3=G3/2. Hence making D3 dependent on itself and hence the circular reference. Now something like this is not very common in everyday mathematics. But if you are trying to model an algorithm, this is very much required. And this is what I was trying to do.

While this is a simple example and you can very easily just write it such that multiple iterations are performed by just dragging, financial calculations usually used to run into 20-30 columns. Think forex forwards and interest rate swaps. And when you model these calculations in software, you use a lot of loops, be it if else, while, etc etc.

Now the problem comes when you have to test these algorithms. It is not enough to test the final result, you have to test each step. Which means you need some s/w which lets you simulate the condition effortlessly and excel does that, now only if it supported loops (without getting your hands dirty by writing macros).

The solution is simple, if the user attempts to do circular referencing, detect it, and automatically move the next iteration below (or to the side, or another sheet), let him choose the number of iterations, and he's a happy man.

Incidentally, this problem and the solution got me into Aditi :)