Subscribe in a readerWorking 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:
Comments
All comments about this page:
Comment by: Michael Mann (10/17/2012 2:54:54 PM)We have several types of cotton and foam that we use, and I am trying to digitalize our inventory system. Is there a way to make it auto go to row 3? I am sorry for asking so many questions, I am just not used to Excel.
Comment by: Jan Karel Pieterse (10/17/2012 3:59:46 PM)Hi Michael,
If you have many (basic) questions like these I advise you to register at www.eileenslounge.com. Many helpful people out there who can answer your questions.
Overhere it's just me :-)
Comment by: Gene (10/18/2013 11:50:36 PM)Hi JKP,
I have $10.00 in C5. I use C6 to receive additional dollars. Today I received $1.25 and placed it into C6. I want that $1.25 to go into C5 making it $11.25. And I want C6 to continue to have $1.25 in it, until tomorrow or the next day. Sometime in the future I will place $.95 into C6, replacing the $1.25. I would like C5 to now contain $12.20, and C6 to contain $.95, until I update it at some future date.
Any help will really be appreciated.
Best regards,
Gene
Comment by: Jan Karel Pieterse (10/19/2013 4:19:48 PM)Hi Gene,
This can be done using a deliberate circular reference and iteration set to 1, but it will cause problems if you somehow cause an extra calculation of Excel.
I would advise to enter each transaction on a new row and have a simple sum function totalling them.
Comment by: Brian (12/4/2013 4:14:37 PM)Hello JKP,
I am using circular references in an excel based stock trading platform. Generally they are set too lock in the last trade price from a stream when a buy or sell order is placed. I want to do some calculations in another cell using the locked in number from the circular reference. When I set the formulas the formula builder usually shows the correct result but the cell where the result goes displays 0 instead of the calculation result.
Any thoughts are appreciated.
Brian
Comment by: Jan Karel Pieterse (12/4/2013 4:17:13 PM)Hi Brian,
What happens if you hit F9, does it then update to the proper value?
Perhaps the number of iterations is incorrect?
Comment by: Brian (12/5/2013 2:28:05 AM)Hello Jan,
Hitting the F9 key does not seem to have any affect. The iterations are set to 1 as it is a large spreadsheet and once a value is locked in it needs to stay until the next day. The circular references are the result of if/then formulas.
Thanks,
Brian
Comment by: Jan Karel Pieterse (12/5/2013 7:49:10 AM)Hi Brian,
OK. In that case I'm afraid I cannot really help without having your workbook.
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.