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. Follow

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
Reply:

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...

Please help..with a formula. I don't know code.
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...

DEADLINE... PLEASE HELP! Stacked Bar chart?
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...

please help with this query
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...

Hyperlink File Help
I am needing some major help. I have a file with hyperlinks in column F that link to a file on our server. I am needing to test to see if the file exists and if it does, copy the file to a folder in my documents called (CapturedFiles) and if it doesn't format the cell color to red. Can VBA do this and if so how? Any help would be greatly appreciated. Thanks in advance. Fileserver or webserver ? Tim On Nov 23, 7:20=A0am, Aaron <Aa...@discussions.microsoft.com> wrote: > I am needing some major help. =A0I have a file with hyperlinks in column = F that > l...

Help please user not showing in 5.5 GAL but is in exchange 2003 GA
Up until today I have been bable to add users fine and their address would appear in both the 5.5 GAL and the exchange 2003 GAL. Is a single site with 2 5.5 servers and 1 exchange 2003 server. When I add a new user now through users and computers and put the mailbox on the new exchange 2003 server the user gets his email addresses and appears in the GAL on the 2003 server but people connected the the old 5.5 servers cannot see it. When I open the 5.5 exchange admin tool again if connected to one of the old 5.5 server I cannot see the person I just created but when connected the the 20...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

help with a sub
Hi, can anybody tell me why the following code fails at FormatConditions.Add Private Sub CommandButton1_Click() Dim Sh As Worksheet Dim lngLastRow As Long Set Sh = ActiveWorkbook.ActiveSheet lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("A4:E" & lngLastRow).Activate Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub Thanks -- Traa Dy Liooar Jock You have an extra open paren just before MOD: &qu...