Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Circular References
Deze pagina in het Nederlands

Working with Circular references in Excel

Introduction

If you've come to this page, chances are you've experienced the "Circular reference warning" popping up when you opened an Excel file or entered a formula. Excel detects a circular reference when a chain of calculations visits the same cell more than once. Many users get (very) confused by this message and have no idea what it is about. I'll try to demystify that message here!

You may also find an excerpt of parts of the article on the Microsoft Office blog

The next couple of pages discuss:

Table Of Contents


Comments

Showing last 8 comments of 32 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (11-7-2014 16:59:05)

Hi Luiz Carlos,

Your solution sounds perfectly feasible to me!

 


Comment by: Steve (14-4-2015 12:58:13)

Hi Jan

I have what I thought was a simple sum but is proving difficult to solve.

I want to create a running total on one row over only 2 columns.

Eg. Column a1 is a number I input on a daily basis. Column b1 will be the sum of a1:b1


So on day one b1 is blank and I enter 2 in a1. B1 then becomes 2.

The next day I change a1 to 3. I then want b1 to become 5

On the third day I change a1 to 5. B1 should then become 10 and so on for each day.

As a slight confusion I only want to do this for 7 days and then I want to delete all the data but keep the formulas and start again.

This is proving to be much harder than anticipated.

Any suggestions?

 


Comment by: Jan Karel Pieterse (15-4-2015 20:07:37)

Hi Steve,

This should work:

In B1:

=IF(C1=7,0,IF(A1="",B1,B1+A1))

In C1:

=IF(C1=7,0,IF(A1="",C1,C1+1))

Turn on iteration and set max iterations to 1.

But it is risky, as if Excel triggers calculation from another cell, the total is updated by whatever is in cell A1. So I would suggest to simply have 7 cells which you enter the numbers into and have a SUM beneath it. WHen the 7th cell is filled, simply clear A1:A7.

 


Comment by: Matthew (16-2-2020 08:45:00)

Hi Jan, thanks for this info!
I've got a budget to build a house that is constantly being updated and the total sum at the bottom adjusts accordingly. Some of the figures within the budget are a percentage of the total budget so I'd like that cell to equal the exact same value of the total budget and adjust as the budget changes. This causes a circular reference issue, but I want it to operate like this. Can I turn off the warning or something simple or can you please explain to me how to fix this?

 


Comment by: Jan Karel Pieterse (17-2-2020 09:36:00)

Hi Matthew,

Sure, simply turn on iteration on Excel's calculation settings. Keep an eye on the results as it calculates to make sure it doesn't run away (values becoming larger and larger or smaller and smaller).

 


Comment by: Matthew (17-2-2020 10:15:00)

That did the trick! Nice and simple when you know how! Thanks a lot Jan! :)

 


Comment by: Matthew Wood (20-2-2020 02:42:00)

Hi again Jan,
This trick worked but it seems to keep breaking and returning a #N/A the next day after fixing it. Enable Iterations is still checked under the formula options??
Thank you!
Matt

 


Comment by: Jan Karel Pieterse (20-2-2020 13:46:00)

Hi Matthew,

I'd suggest to make sure you can set a starting value for your circular reference as I explain on this page: https://jkp-ads.com/Articles/circularreferences03.asp

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.