Circular references
iStock/Andrey Prokhorov
Circular references in Excel
Tags:

How to Use Circular References in Excel

by
Mar 11th 2015
Share this content

If you’ve used Microsoft Excel for any length of time then most likely you’ve inadvertently created a formula that results in a circular reference. This means that the formula you created includes the cell where the formula resides. In most every instance in Excel when we create formulas we refer to cells other than the one where our formula resides. However, there are situations where you may want to purposely create a circular reference.

You’ve most likely created an accidental circular reference by creating a SUM that includes the total row itself, as shown in Figure 1. The results that you see will vary based on your version of Excel:

  • Excel 2013—A simple prompt will appear informing you that you created a circular reference, giving you the option to choose OK or Cancel. If you click OK, the formula appears in the cell but might display zero, as shown in Figure 1.
  • Excel 2010 and earlier—You might have the impression that your computer is about to crash. Not only does Excel give you a warning prompt, but it also draws one or more arrows on the screen and displays a help screen.

Figure 1: The formula displays zero because you have created a circular reference.

In every version of Excel the words Circular Reference will appear in the status bar, along with a cell reference. All of these indications go away once you correct the formula.

However, there can be situations where you may wish to intentionally create a circular reference. My go-to example when teaching circular references harkens back to my days when I worked in commercial real estate. Let’s say that a real estate developer has lined up the financing to build a $100 million building. The developer will earn a developer fee for its efforts; in this case let’s say 3 percent. However, the contracts get written so that it’s not 3 percent of the building cost, but rather 3 percent of the total project cost. The total project cost is the $100 million plus the developer fee. Therein lays the circular reference, as the fee is part of the calculation. Figure 2 shows the formulas involved.

Figure 2: Cell H3 contains the formula SUM(H1:H2), so the developer fee initially doesn’t calculate.

Notice that initially the developer fee doesn’t calculate. In this situation we need to instruct Excel to figure out the developer fee for us by setting an Excel option, as shown in Figure 3:

  • Choose File, Options, and then Formulas.
  • Click the checkbox for Enable Iterative Calculations, and then click OK.

Figure 3: Enable Iterative Calculations to calculate the developer fee.

Note that this is a workbook-specific setting, so you’ll have to enable this setting for each individual workbook that will involve circular references. Once you close the Options dialog box you’ll see that Excel calculates the developer fee of $3,092,784. Writing the contract so that the development fee is a based on the total cost gives the developer an extra $92,784, since 3 percent of the building cost itself would have been $3,000,000.

If we refer back to Figure 3, you’ll notice that there are two additional settings related to iterative calculations:

  • Maximum Iterations: The default value is 100, which means Excel will try 100 times to resolve the calculation before it gives up. This setting protects you from putting Excel in a state of perpetual calculation.
  • Maximum Change: Excel tries up to 100 times to resolve the calculation, and stops when the difference between iterations is no more than .001.

I can only think of a couple of times in my career that I’ve needed to tweak either of these options. In my experience changing the Maximum Change to .01 should suffice for most instances. Make sure that you save your workbook before you tinker with the Maximum Iterations or Maximum Change in case you accidentally put Excel in a state that you can’t get out of without crashing the program.

In an upcoming article I’ll describe a hidden feature in Excel that makes it easy to find all circular references within a workbook. Keep in mind that circular references must be used with care in Excel. If you simply enable iterative calculations to make the prompts and arrows go away you can end up with misleading results. For instance, that simple sum function that I created returns 60,000 instead of 600 if Enable Iterative Calculations is turned on, as shown in Figure 4.

Figure 4: Be careful enabling Iterative Calculations because they can lead to misleading results.

Tags:

Replies (3)

Please login or register to join the discussion.

avatar
By Col D
Jun 26th 2015 01:12

In regard to the building development example, why not just use a simple algebraic solution rather than add risk to your spreadsheet by enabling circular references?

The formula in cell H2 (to calculate the developer's fee @ 3%) needs to be:
=$H$1/(1-$I$2)*$I$2 (Note that I've made the references absolute just so they can be easily distinguished as the column reference I looks a lot like a 1)

Thanks (0)
Replying to Old Cat Woman:
avatar
By Michael
Jun 26th 2015 01:12

Probably because a lot of people don't think in algebraic terms, but your solution is elegant. For those of us that might want the steps involved to how Col D got it, do the following:
We know that Developer fee X + 100,000,000 = Y (total cost).
We know that the developer fee is 3% of Y
So we can rewrite equation in terms of Y
.03Y + 100,00,000 = Y
subtract .03y from both sides
100,000,000 = 0.97Y
Divide by .097
Y = 103,092,784
Times by .03 to get developer fee or subtract 100,000,000.
Developer fee = 3,092,784.
Once you do that and understand it, you can make the equation that Col describes easily.

Thanks (0)
avatar
By Pat Curley
Sep 16th 2017 20:31

As a long-time daily user of Excel, I strongly recommend against allowing circular references in your spreadsheets (and against enabling iterative calculations). As you note, it can lead to errors, which would be pretty embarrassing if you're analyzing a $100 million transaction. In the example you cited I agree with Col D that it is less risky to use algebra so that there is no circularity.

Plus since spreadsheets are commonly shared, you would have to make sure that everybody you sent it to knows how to enable the iterative calculation.

However, sometimes circular references are harder to avoid. Let's look at your building example. Suppose the lender charges a 1% loan fee and provides an 80% loan to cost. That part you can still handle with the algebra, but now add another wrinkle: because it's new construction, the lender will be funding the interest as well. Thus the interest itself is another part of the cost of the project, and so the loan fee is in part based on the interest, and at least some of the interest is based on the loan fee.

What I strongly recommend is essentially doing the iterative calculation yourself. Plug in a number for interest in calculating the total cost, then check that against the number the spreadsheet has calculated elsewhere. Not right? Try again. I'll usually put a checker in the next cell. If the two interest amounts are close enough it displays "Ok" and if not it says "Error".

It's annoying to have to do it, and it gets even worse when you add additional layers like mezzanine financing; sometimes you need to go back and forth between two plugged numbers several times before both are within the rounding tolerance. But it's better than having errors creeping in from circular references.

Thanks (0)