When Circular References In Excel Actually Help

Circular references can cause major problems in Excel and other spreadsheets -- so much so that most programs will sternly warn you off if you accidentally create one. But there can be occasions when you actually need them.

A circular reference is a formula in a spreadsheet which refers to the cell where the formula is located. For instance, if you type the formula =A1+1 into cell A1, it's impossible to calculate, since the cell has to use itself as part of the calculation. If you enter this kind of formula, Excel pops up a warning to make sure you don't inadvertently mess up your results or crash your spreadsheet.

However, as Excel expert Jan Karel Pieterse points out, there may be occasions when you do want a cell to refer to itself. One example is a cell which calculates when another cell had an entry made into it. If you use the circular formula =IF(A1="",TODAY(),B1) in cell B1, B1 will track when changes happen to A1.

While circular references can be useful in this way, they require careful planning if you don't want to be constantly bombarded with warnings. Hit the post for a detailed discussion of the issues.

Working With Circular References In Excel [via Excel Blog]


Comments

    Now if only MS SQL Server allowed circular references like every other major database system. Would have avoided a lot of grief with one of our clients. If it were up to me I'd use MySQL.

      You should never have a situation where you *need* a circular reference. It's only use is for hacks to save time, never something you would want in place in a live system where stability is paramount.

    These are good for setting up iterative calculations that converge on a figure after several loops.

    We used to use them in university to do all sorts of wonderful things, but you had to limit the number of times the formula would calculate before stopping.

    Iterative calcs are more safely done by Goal seek. In many cases I find people ask for a circular solution when all they have to do is backsolve using a net to gross calculation.
    http://www.accountingweb.co.uk/group-thread/circular-reference-help

Join the discussion!