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

All comments about this page:


Comment by: Lazyprioria (8/3/2012 12:25:17 AM)

excellent post, very informative. I wonder why the other specialists of this sector do not notice this. You must continue your writing. I am confident, you have a great readers' base already!

 


Comment by: Lauri Anne (8/3/2012 3:48:58 AM)

Can you use a circular reference in Sky Drive. I have a fairly large sheet that uses circular references to distribute orders to our produce co-op. I would like to be able to use this sheet on SkyDrive but it looks like that can't happen.

 


Comment by: Jan Karel Pieterse (8/7/2012 11:17:49 AM)

@Lauri: I guess the answer must be no if you have tested it and it does not work. But did you try it with a small spreadsheet?

 


Comment by: Lauri Anne (8/7/2012 3:35:35 PM)

I have tried it it seems the problem is SkyDrive doesn't support the circular reference nor does it support iterative calculations...

 


Comment by: Jan Karel Pieterse (8/8/2012 1:08:58 PM)

Hi Lauri Anne,

OK, thanks for letting me know!

 


Comment by: Michael Man (10/16/2012 9:00:19 PM)

I am attempting to create an inventory control spreadsheet. In it I have created a circular reference and do not know how to resolve it. I have started cell B2 to be my starting inventory with C2-G2 being Monday-Friday usage and H2 being final weekly counts. Is there a way after fridays usage is entered and is put into H2, that B2 gets the new value as well to start over again for next week?

Thank you for your time.
Michael J Mann

 


Comment by: Jan Karel Pieterse (10/17/2012 9:32:23 AM)

Hi Michael,

Is there a reason why you would not simply start the next week on row 3?

 


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.

 


Comment by: richie (1/21/2014 4:14:09 AM)

anyone help me,always shown #N/A





=VLOOKUP(B8,CHOOSE(VLOOKUP(A8,Sheet3!F2:G36,2,FALSE),Sheet3!B2:C4,Sheet3!B6:C8,Sheet3!B10:C12,Sheet3!B14:C16,Sheet3!B18:C20,Sheet3!B22:C24,Sheet3!B26:C28,Sheet3!B30:C32,Sheet3!B34:C36,Sheet3!B38:C40,Sheet3!B42:C44,Sheet3!B46:C48,Sheet3!B50:C52,Sheet3!B54:C56,Sheet3!B58:C60,Sheet3!B62:C64,Sheet3!B66:C68,Sheet3!B70:C72,Sheet3!B74:C76,Sheet3!B78:C80,Sheet3!B82:C84,Sheet3!B86:C88,Sheet3!B90:C92,Sheet3!B94:C96,Sheet3!B98:C100,Sheet3!B112:C114,Sheet3!B116:C118),2,FALSE)

 


Comment by: Jan Karel Pieterse (1/21/2014 6:13:07 AM)

Hi Richie,

What is wrong with a simple:

=VLOOKUP(B8,Sheet3!B2:C118,2,FALSE)

I suspect the structure of your worksheet is making it hard to find what you need for your calculation...

 


Comment by: richie (2/8/2014 7:44:13 AM)

Jan Karel Pieterse

hope you don't mind, can you give me your email add!,then i'll send you my format...

=)

 


Comment by: Chris (4/16/2014 7:15:59 AM)

Hi Jan, I have i hope a basic question .
wish to input a value into a cell and multiply it by another cells value, but have the result show in the first cell.

b1 = a1*b1 . so that the formula is just =a1* the value that will be entered into bi at another time. i'd like not to have to enter the value in the formula bar but in the cell, if at all possible.

this is to create a daily calories calculator

regards chris

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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