#### Help needed with Circular References

```Hi,

I am developing a Excel based solution and that requires
circular reference in the formulas. However I am ok with
setting the limit that each cell be calculated only once.
My question is what the order of recalculating cells is.
Is it row first starting from A1 or column first starting
from A1 or is it the order of entering the cell formulas?

Depending upon the order in which the cells are
calculated results can change. For example, if the
formulas in the cells A1 to A5 are as follows (value in
the bracket indicates initial value when formula is
entered):

A1: A5+1 (1)
A2: A1+1 (2)
A3: A2+1 (3)
A4: A3+1 (4)
A5: A4+1 (5)

If cells are calculated in order of A1-A2-A3-A4-A5 then
each time I press F9 cell values of A1-A2-A3-A4-A5 would
be 6-7-8-9-10, 11-12-13-14-15 and so on.

However if cells are calculated in order of A3-A4-A5-A1-
A2 then each time I press F9 cell values of A1-A2-A3-A4-
A5 would be 6-7-3-4-5, 11-12-8-9-10 and so on.

So the resultant value of each cell depends on order of
cell calculation. Is there any specific sequence in which
these cells are calculated?

The example I gave above is simple, but the solution that
I am developing has circular references spanning across 2
worksheets and lots of cells spread all over those
worksheets.

Thanks
Yogi Watcher
```
 0
anonymous (74722)
7/8/2004 5:10:37 PM
excel.misc 78881 articles. 5 followers.

3 Replies
247 Views

Similar Articles

[PageSpeed] 52

```Since you have solutions on different sheets, the recalculation method
will be different for XL2002/3 vs. previous versions. Here's an
explanation of XL's recalc process:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k
2/html/odc_xlrecalc.asp

In article <29d1a01c4650e\$7ce92c20\$a301280a@phx.gbl>,
"Yogi Watcher" <anonymous@discussions.microsoft.com> wrote:

> Hi,
>
> I am developing a Excel based solution and that requires
> circular reference in the formulas. However I am ok with
> setting the limit that each cell be calculated only once.
> My question is what the order of recalculating cells is.
> Is it row first starting from A1 or column first starting
> from A1 or is it the order of entering the cell formulas?
>
> Depending upon the order in which the cells are
> calculated results can change. For example, if the
> formulas in the cells A1 to A5 are as follows (value in
> the bracket indicates initial value when formula is
> entered):
>
> A1: A5+1 (1)
> A2: A1+1 (2)
> A3: A2+1 (3)
> A4: A3+1 (4)
> A5: A4+1 (5)
>
> If cells are calculated in order of A1-A2-A3-A4-A5 then
> each time I press F9 cell values of A1-A2-A3-A4-A5 would
> be 6-7-8-9-10, 11-12-13-14-15 and so on.
>
> However if cells are calculated in order of A3-A4-A5-A1-
> A2 then each time I press F9 cell values of A1-A2-A3-A4-
> A5 would be 6-7-3-4-5, 11-12-8-9-10 and so on.
>
> So the resultant value of each cell depends on order of
> cell calculation. Is there any specific sequence in which
> these cells are calculated?
>
> The example I gave above is simple, but the solution that
> I am developing has circular references spanning across 2
> worksheets and lots of cells spread all over those
> worksheets.
>
> Thanks
> Yogi Watcher
```
 0
jemcgimpsey (6723)
7/8/2004 11:05:35 PM
```This is very good information, however that article does
not mention anything about recalculation process if there
are circular references in the formulas.

I am particularly interested in recalculation process
(ordering cells for recalculation and performing actual
recalculation) when there are circular references becuase
my solution involves circular references.

If there are no circular references then recalculation
order is important only for optimization not for results
(results will alway be correct). It is when there are
circular references order of recalculation becomes
important for optimization and for results.

What exception does Excel performs internally if it
encounters circular references?

>-----Original Message-----
>Since you have solutions on different sheets, the
recalculation method
>will be different for XL2002/3 vs. previous versions.
Here's an
>explanation of XL's recalc process:
>
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnexcl2k
>2/html/odc_xlrecalc.asp
>
>
>In article <29d1a01c4650e\$7ce92c20\$a301280a@phx.gbl>,
> "Yogi Watcher" <anonymous@discussions.microsoft.com>
wrote:
>
>> Hi,
>>
>> I am developing a Excel based solution and that
requires
>> circular reference in the formulas. However I am ok
with
>> setting the limit that each cell be calculated only
once.
>> My question is what the order of recalculating cells
is.
>> Is it row first starting from A1 or column first
starting
>> from A1 or is it the order of entering the cell
formulas?
>>
>> Depending upon the order in which the cells are
>> calculated results can change. For example, if the
>> formulas in the cells A1 to A5 are as follows (value
in
>> the bracket indicates initial value when formula is
>> entered):
>>
>> A1: A5+1 (1)
>> A2: A1+1 (2)
>> A3: A2+1 (3)
>> A4: A3+1 (4)
>> A5: A4+1 (5)
>>
>> If cells are calculated in order of A1-A2-A3-A4-A5
then
>> each time I press F9 cell values of A1-A2-A3-A4-A5
would
>> be 6-7-8-9-10, 11-12-13-14-15 and so on.
>>
>> However if cells are calculated in order of A3-A4-A5-
A1-
>> A2 then each time I press F9 cell values of A1-A2-A3-
A4-
>> A5 would be 6-7-3-4-5, 11-12-8-9-10 and so on.
>>
>> So the resultant value of each cell depends on order
of
>> cell calculation. Is there any specific sequence in
which
>> these cells are calculated?
>>
>> The example I gave above is simple, but the solution
that
>> I am developing has circular references spanning
across 2
>> worksheets and lots of cells spread all over those
>> worksheets.
>>
>> Thanks
>> Yogi Watcher
>.
>
```
 0
anonymous (74722)
7/9/2004 6:45:06 PM
```Sorry - don't think I've ever seen a more complete explanation of how XL
builds the calculation table. The answer will certainly depend, at least
initially, on what order the CRs are entered. However, that dependency
tree can be rebuilt in XL02/03 using CTRL-ALT-SHIFT-F9. After doing
that, you could test to determine the order.

In article <2a1e001c465e4\$da724fd0\$a401280a@phx.gbl>,
"Yogi Watcher" <anonymous@discussions.microsoft.com> wrote:

> This is very good information, however that article does
> not mention anything about recalculation process if there
> are circular references in the formulas.
>
> I am particularly interested in recalculation process
> (ordering cells for recalculation and performing actual
> recalculation) when there are circular references becuase
> my solution involves circular references.
>
> If there are no circular references then recalculation
> order is important only for optimization not for results
> (results will alway be correct). It is when there are
> circular references order of recalculation becomes
> important for optimization and for results.
>
> What exception does Excel performs internally if it
> encounters circular references?
```
 0
jemcgimpsey (6723)
7/10/2004 5:57:19 AM

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Using iterations to calculate circular references.
I am working with data that requires using circular references in some calculations. I checked the "iterations" box to allow for these calculations to work, and Ecxel seems to be calculating everything correctly. The problem is that after I close the workbook, when I later reopen it to continue working, all the cells that are a part of the circular reference display the #VALUE! error. I can fix this by deleting one cell that is part of the loop and then undoing that command, or by copying the formula from a cell, deleting it, and pasting it back into the same cell. I have to d...

REQ: Can Someone Help Me With This Outlook XP Question?
Hello All: I use Word to edit my e-mail msgs in Outlook XP. I had to reinstall Office the other day and now whenever I want to start a new e-mail or reply to an e-mail I get a warning that comes up: "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected it may be a virus and you should choose 'No'" There is a box that asks for the amount of time to allow the access: 1 to 10 minutes. Do I have to have specific settings for my Outlook address book? I use the Contact area in Outlook for addresses. I have ...

IWAN & IUSR bei Crystal Reports? *help*
Hi NG, my problem is that i can see the crystal reports, but when i open one i get "more information is needed". But till yesterday i could open reports!! Now after some search i see that i have no IUSR_servane and no IWAM_servername. how could this happen? My system is AD, SQL, CRM and each of them is one a seperate 2003 server. Please let me know if you have any suggestions. Regards Nicolas F�hrs sound strange with the IWAN and IUSR. I offten have this problem. There are a techknowledge article with 13 resoluti...

I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

Need online services to the end of the year
I have been a loyal Money customer for years. This morning my Money Plus subscription expired. There seems to be no way to extend it and without it I can't do updates from my bank and credit cards--which I do daily. I understand that everything is shutting down by Jan 11, but it is terrible not to be able to extend basic online services temporarily at least through the end of this year to get me through tax season, etc. and then switch to Quicken at the beginning of 2010. This really sucks! Please, please Microsoft give us at least a stay of exectution through the end of the ye...

Help With Strange Error When Trying to Save Any Record
I am getting astrane error happens when try to create any new record (Customer, Item, Vendor or Account), the system gives me a message that "Save Operation Failed" and when i click "More Info" the error message is "Could not find stored procedure 'DYNAMICS.dbo.aagGetCompanyStatus'", although i have created alot of items, customers, accounts and vendors before but suddenly this error appeared. Any quick help will be highly appreciated. It would appear that the AAG has something to do with a product produced by American Association of Geographers. I...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

help with preview pane and "read receipts"
Hi, I'm new to Outlook administration. We run Outlook on a server wit Exchange. Is it possible to setup Outlook on client PC's so that th preview pane cannot be activated by individual users? Also, can the blocking of "read receipts" by individual PC users b prohibited within Outlook, at the server level? If not, is there an other way to do it? My reasoning for wanting to do the above two things is to make th "read receipts" function work more effectively. Thanks for your suggestions. : ----------------------------------------------- ~~ Message posted from h...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

Not allow entering repeated references in a column
Frank, If you don�t mind ... I will send it :) . As I don�t know your e-mail, I will give mine, which is ssouritinha@sapo.pt Just send your e-mail to my e-mail box and I will send the file. Thanks, Ritinh -- ritinh ----------------------------------------------------------------------- ritinha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1566 View this thread: http://www.excelforum.com/showthread.php?threadid=27195 ...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =\$C\$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Help with Do...Loop
Hi I need a check to be done to see if column a has a number in it then to check if column b has a number. If column B doesn't have a number then I need it to stop and give a msgbox, When column A doesn't have data then I need the loop to stop as we dont have to check column b The code below is what I have but when I try to run it it keeps saying LOOP WITHOUT DO. I hope someone can help me as I am not very good with loops. mykeycode = Range("B32") mysell = Range("N32") Do mykeycode = mykeycode + 1 mysell = ...

Help on Macro or Formula
Hi, i hope someone can help me. i need to create a formula that sits in a cell and looks for data. ( obvioiusly ). however, the formula needs to be in place even though the file from ehere the data comes from might not be there yet. ( i have to create a book that when a new file is created, the links are already in place ). i think it could work with an IF type formula for ( if B2="",""). here is my information. Cell description: A2 = Job no. B2 = Client Name D2 = Actual Spend on project Register!D2 = Job Description Register!H2 = Quoted Amount my path is S:\Clients\...

Office 2010 Buying Question Assistance Needed
I've been looking through the MS Office 2010 web site to try to determine what my new small company would require, but I can't find the information I need. We for sure would need Office Pro Plus, but other than that I'm not sure. We want to run it on our own server. We will initially have 3-5 people using it and perhaps more later on. Would we need to purchase site licensing? Unfortunately, our programmers are MS haters (I'm not) and I can't get any assistance from them on this, but I have power of the pen. I would appreciate any assistance I can get. Th...

Need help with update sql plus filter
I have the following update sql (copied from the query design view) UPDATE ListQry SET ListQry.ApprovalStatusID = [Forms]![OpeningForm]![Responsibility] WHERE (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID)>300)) OR (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID) Is Null)); ApprovalStatusID is an integer OtherStatusID is an integer ListQry is the recordsource for my form. I would like to add the f...

HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE
My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 and e1 the cells d1 and e1 have the following formula : d1=c1-b1 e1=d1-c1 but i want the following d1 should be b2-a2 and e2 should be b3-a3 how do i do this? One way... In C1 enter & copy across: =INDEX(\$A:\$A,COLUMN()-COLUMN(\$C\$1)+1)-INDEX(\$B:\$B,COLUMN()-COLUMN(\$C\$1)+1) anantth wrote: > My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 > and e1 the cells d1 and e1 have the following formula : > d1=c1-b1 > e1=d1-c1 > > but i want the following > d1 should be b2-...

Reference: many to one
I have a form in which users enter information...I keep all controls locked until the user hits a command button to unlock particular bound controls. This works really well, but becomes cumbersome to code when new controls are added; e.g., adding the new control to the several event procedures attached to the form. Can anyone tell me how to refer to a group of controls once, e.g., give them a name; and then only have to refer to that name in the future? alex Alex I'm having trouble visualizing your situation. It sounds like you are describing a form that is being regularly modified....

Formulas in Excel using [R-1] not A10 references
I converted a Word document into Excel 2003 and when creating formulas,they are formatted as the cell the formula is in plus or minus a number of rows or columns. An example: =SUM (R[-4]C:R[-1]C). I would like to see these formulas in the regular format referencing Row and Column number i.e. =Sum A1:V52 Tools >Options, the General tab, uncheck R1C1 reference style "Drregion" wrote: > I converted a Word document into Excel 2003 and when creating formulas,they > are formatted as the cell the formula is in plus or minus a number of rows or > columns. An example...