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
Showing last 8 comments of 27 in total (Show All Comments):Comment by: Jan Karel Pieterse (4/16/2014 12:09:57 PM)Hi Chris,
Why not show the result in a different cell than the input cell? That is the better aproach because you'll be able to see both input value and result.
Comment by: Marnix (4/20/2014 10:52:52 AM)Hi JAn, hope you can help. I made a cricular reference in a cell, say cell A, that is accumulating a value from another cell, say cell B. However, the automatic sheet calculation makes that it updates cell A every time a change is made anywhere due to auto calculation. I only want to have cell A updated if I make a change to cell B. I do not want to set the sheet to Manual calculation. (PS: Iteration is set to 1). Thank sin advance, Marnix.
Comment by: Jan Karel Pieterse (4/28/2014 10:17:58 AM)Hi Marnix,
You can only do that if the value in the "watched" cell is changed from a fixed value (for instance empty) to something else:
=IF(A1="";Now();B1)
Comment by: Marnix (4/28/2014 9:45:55 PM)Hello Jan, many thanks for your suggestion. Translated to my case this did the trick: =IF( B1<>"" , A1+B1 , A1).
Cell A1 now accumulates any update of cell B1 without calculating other cells.
Thanks again!
regards Marnix
Comment by: Luiz Carlos Guilarducci (7/10/2014 5:17:27 PM)Let me explain my troubles, I'm using circular reference in my model to calculate interest income (My model is yearly, and i use the average between my cash balance from the year before and the current year). When I change the start date, my interest incomes go to infinite, and i need a cells to break the circularity and after return with it.
As my interest income are going to infinite, i use automatically a formula to chose 0 if interest income go to inifnite, otherwise 1. It is working well, but my boss doesn´t like this solution because normally people use cells to choose the circular reference or not.
I wanna know, if really there is a problem with my solution. What do you think? Have you ever used this solution?
Regards Luiz !
Comment by: Jan Karel Pieterse (7/11/2014 4:59:05 PM)Hi Luiz Carlos,
Your solution sounds perfectly feasible to me!
Comment by: Steve (4/14/2015 12:58:13 PM)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 (4/15/2015 8:07:37 PM)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.
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.