Specific errors: Circular references (part 1)

I mentioned earlier that one very common error code in spreadsheets is the “circular reference”. There’s a lot to discuss about circular references, so in this post I’ll cover the definition of the error and what it looks like in various spreadsheet programs. Next week I’ll cover how to track down the source of the problem, and common causes/solutions.

circular reference: noun. See circular reference.

That joke’s an old one, at least in computer programming circles, but it’s still valid. A circular reference in a spreadsheet is when you try to calculate something based on its own result. For example, =A1+1 would be fine most places, but it would yield a circular reference error if the formula were placed in cell A1.

The circle can also include more than one step. If you define a circular reference as a circular definition, and then define a circular definition as a circular reference you still have a circle. Or, in a spreadsheet setting, imagine that you’re trying to calculate two children’s ages, based on the sort of information you’d get from a story problem. Saying that Billy is six and Sally is twice Billy’s age would work. Saying that Sally is twelve and Billy is half Sally’s age would also work. But saying that Billy’s age is half of Sally’s age, and that Sally’s age is twice Billy’s age…that may be true, but spreadsheets don’t care about what’s true. They only care about what they can figure out. If a spreadsheet can’t figure out an actual result it’s going to return an error, and in this case the error’s going to be a “circular reference” error.

Here’s a quick diagram to illustrate the point, ala Punnett squares.

Billy = 6 Billy = 0.5 * Sally
Sally = 12 No calculation needed Good: 0.5 * 12 = 6
Sally = 2 * Billy Good: 2 * 6 = 12 BAD: 0.5 * 2 * …?

Most spreadsheets will give you some sort of warning you that you’ve created a circular reference. Some will automatically try to launch you into a wizard to help you solve it, others will offer you instructions on how to use their built-in tools, and yet others will offer nothing more than the initial warning.

In Excel 97:

  • Visible cell contents: 0 (with a blue dot, if the “circular reference toolbar” is activated and the reference is directly to itself, and/or blue arrows if the references go to other cells)
  • Warning location: Automatic pop-up
  • Warning text: Microsoft Excel cannot calculate a formula. Cell references in the formula refer to the formula’s result, creating a circular reference.
  • Help offered: Instructions on built-in tools.

In Excel 2007:

  • Visible cell contents: 0
  • Warning location: automatic pop-up
  • Warning text: Circular Reference Warning
  • Help offered: Instructions on built-in tools.

In Google Spreadsheet:

  • Visible cell contents: #REF!
  • Warning location: pop-up when pointer hovers over cell
  • Warning text: error: circular dependency detected
  • Help offered: none

In OpenOffice Calc:

  • Visible cell contents: Err:522
  • Warning location: bottom border
  • Warning text: Error: Circular reference
  • Help offered: none

In Microsoft Works Spreadsheet:

  • Visible cell contents: 0
  • Warning location: pop-up
  • Warning text: Your formula contains a circular reference
  • Help offered: none