reference to circular reference bug

I just discovered something that looks like a new excel bug:
to reproduce behaviour:

1. open a new workbook;
2. activate circular references;
3. enter those formulas:
B4 <- formula is =B5
B5 <- formula is =1+SE(B6;0;1)
B6 <- formala is =(B5=2)
B7 <- formula is =B5 (same as B4!!!)

(NB: SE() this is the standard IIF() function in the italian version
of excel;)

What's wrong: B4 and B7, will display different results even if they
contain the same formula. The result displayed depends on the position
of the cell conataining the formula: if it is on the left or above B5
the result will not be equal to B5.

My explication: The cells before B5 are computed one iteration before
the last, so they are not up to date. I suppose that mr. M$ will say
that this behaviour is by design...

Comments welcome!
Marco
0
m.p.b (2)
12/18/2003 5:01:24 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1015 Views

Similar Articles

[PageSpeed] 55

The reason is that your circular calculation never converges, it just hits
the maximum number of iterations.

The way circular calculations work is left-to-right, top to bottom.

even if you move the formula in B4 to below it still does not converge
because the cells are changing by more than Maximum Change on each
iteration.

Does not seem fair to blame M$ for failing to solve a non-converging
process: basically there is no "correct" answer to your problem, only
several different ones.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"marco" <m.p.b@rocketmail.com> wrote in message
news:a7bb181b.0312180901.4cb374f5@posting.google.com...
> I just discovered something that looks like a new excel bug:
> to reproduce behaviour:
>
> 1. open a new workbook;
> 2. activate circular references;
> 3. enter those formulas:
> B4 <- formula is =B5
> B5 <- formula is =1+SE(B6;0;1)
> B6 <- formala is =(B5=2)
> B7 <- formula is =B5 (same as B4!!!)
>
> (NB: SE() this is the standard IIF() function in the italian version
> of excel;)
>
> What's wrong: B4 and B7, will display different results even if they
> contain the same formula. The result displayed depends on the position
> of the cell conataining the formula: if it is on the left or above B5
> the result will not be equal to B5.
>
> My explication: The cells before B5 are computed one iteration before
> the last, so they are not up to date. I suppose that mr. M$ will say
> that this behaviour is by design...
>
> Comments welcome!
> Marco


0
Charles740 (233)
12/18/2003 6:45:07 PM
Reply:

Similar Artilces:

How do I reference a cell as a row number in a formula?
The formula I currently have is: =SQRT(SUM(F5280:F6240)/(N3-M3)) the row numbers 5280 and 6240 are values found in cells M3 and N3, respectively. However I need to copy this formula down a column, and I currently must go through and edit each formula to change those two values to the neighboring cells. i.e. that formula is in cell O3, and when copied to O4 the desired rows of column F to be referenced will be the numerical values in M4 and N4. =SQRT(SUM(F____:F____)/(N3-M3)) please fill in the blanks? =SQRT(SUM(INDIRECT("F"&M3&":F"&N3))/(N...

To MS: bug report for Outlook 2003
Hello, I did not find any website to submit a bug report, so I use this newsgroup. Outlook 2003, german, all updates applied (22 july 05) Problems with rules: I have about 15 email accounts defined and about 30 rules. When I create a rule to move received email to an other folder, and when using 'Emails received on this account' this is not working problerly: For some rules the wrong account is used. When I edit the rule in the list a wrong account is displayed (and is used for the rule) but when I click to change the account, the correct account is dispalyed. I have imported 30...

Can't reference 2 other sheets in a third!
Hi All, I have a workbook with 3 sheets. In the third sheet, I'm trying to reference columns in both sheet 1 and 2, to compare data entry by person 1 and person 2, respectively. When I put in the references for one sheet (e.g., ='Bup1'!A2) it's fine. When I do the same in an adjacent column so I can compare the two, it only gives me the formula as if it were just text I typed in. For example, in sheet 3, when referenced, Column A and B are comparing dates from the two entries. Column A shows 9/6/2007, and Column B shows ='Bup2'!A2. I referenced these by entering =...

Bug while editing email subject on Outlook 2002 SP3
Hi All, Has anyone encountered this bug? It's a bit difficult to explain: I write an email, then write the subject of the email. If I try to backspace to edit, it will not do so in the subject, it will backspace in the body of the message, in the exact spot where the cursor was before I clicked to type on the subject. Sometimes when copying something into the subject, when pasting (CTRL+V) it will paste where the cursor was last in the body of the message. Any hints or clues? Thanks, Jonatan ...

Reference function from an out of sort table
I'm looking for a reference function to pull the result from an out of sort table (see table example below). The result should always come from the reference value LT A14. The table is always a whole number 0,1000,2000,3000, etc. I've tried the following functions, but they all require data in ascending order and/or exact match of the value in A14. =VLOOKUP($A$14,A$2:B$10,TRUE) =INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2) =LOOKUP($A$14,A$2:A$10,B$2:B$10) Table example: A B 2 1000 0.525 3 6000 0.946 4 3000 0.675 5 0 0.457 6 4000 0.746 7 2000 0.606 8 8000 1.29 9...

Autoflow bug when publishing to html
I made a newsleter, some text boxes auto flowed into others, saved as html, no proble later I edited the file in publisher and after publishing as html I noticed the previous typing persisted so I now had duplicate paragraphs, looked bad in explorer looked ok in publisher To fix it I pasted the text into separate boxes without autoflow and it worked o Still having a problem where I cant mass mail the publication, I get an instant reply from outloo Your message did not reach some or all of the intended recipients Subject: News from Lease +Loan Maste Sent: 2/18/2004 2:23 P The fol...

Fix multiple 'assign macro' references broken due to moving sheet
I'm mystified as to why this happened, but I'm hoping someone can help me fix it in an easy way... I have a workbook with multiple sheets, and many buttons with assigned macros on different sheets for navigation, filtering, etc. I created a new blank sheet within the workbook for a throwaway calculation, and then decided I didn't want it in that book so right clicked and moved to a new book. That was fine and I proceeded with my calculating for a while. Then I went back to the original book and tried to use a button, but got an error that the macro did not exist, refe...

Why didn't .net 2003 fix known bug
Close to a year ago I asked about a problem I was having where a CDateTime control using the Time format would change to the Date format by itself. I was told this was a known bug. That was with .net 2002. I just upgraded to .net 2003, that bug being one of the reasons, and found it still exists. This is very frustrating. Is there anything that can be done when such a problem persists? I'm using the standard version so I do not have the ability to report bugs, unless I want to pay first of course. And having to pay to report a known problem just rubs me the wrong way. On...

Including tab name in absolute reference
I have lots of spreadsheets that have the same seven tabs in them. How do I automatically ensure that the correct tab is selected when running a macro with absolute references? Is there a way to have the macro select the tab before running the rest of the macro? Brad Excel 2002 on XP Pro SP 3 Excel 2007 on Vista 64 The answer is "NOT TO USE the SELECT METHOD". In your code reference each sheet by its object or Name. Don't use recorded macros without eliminating all the select properties and activate properties (there are only a few cases where this ma...

How do I use a reference that a cell returns
I have a spreadsheet where P3 Contains an address 'Exited!'!$E$10. I want to see if the value on the exited sheet 'Exited!'!$E$10 matches Q3. What function do I use to return the value of 'Exited!'!$E$10. T(P3) or value(P3) doesn't work. Any help is grateful. Use INDIRECT() =INDIRECT(P3) -- Regards Juan Pablo Gonz´┐Żlez "Dim DumbAss as Name" <DimDumbAssasName@discussions.microsoft.com> wrote in message news:8BD3A873-D173-4F5F-86AC-49A21BF24A9F@microsoft.com... > I have a spreadsheet where P3 Contains an address 'Exited!'!$E$10. I...

Can I reference a filtered item in the Custom Header
I would like to include the currently filtered item in my custom report header. For example: "Dummy Bills for Agency xxxxxxxx", where xxxxxxxx is the currently filtered agency. Can this be done? Here's a link that returns the filter criteria via a userdefined function. It's a procedure posted by Tom Ogilvy (posted by Debra Dalgleish). http://groups.google.com/groups?threadm=40BE8AD9.5040505%40contexturesXSPAM.com Jack wrote: > > I would like to include the currently filtered item in my custom report > header. For example: "Dummy Bills for Agency xxxxxxx...

Reference constant cells in different files from a master workbook
We need to have master spreadsheets that summarize value from different sets of identical excel spreadsheets. We have a multi-tab template spreadsheet that gets filled in with the data for each shipment and saved. The layout is exactly the same for each shipment and only item serial numbers change. We then generate another summary spreadsheet report periodically that consolidates the serial items and numbers from the individual shipments. Right now we are just pasting the serial numbers from the individual shipment spreadsheets into the summary spreadsheet. I know I can refe...

Column/Row reference errors
I have a 4Mb spreadsheet within which the row and Column reference headings (eg. A,B,C and 1,2,3,etc...) have become 'strikethrough'. Any ideas? Has this sheet become corrupted? (Track Changes are not enabled. There are about 4000 rows of 10 column data. There are no formulas or conditional formats to speak of) This is the answer I posted to the same question a week ago. Check out Format>Style>Normal. Modify the Font to eliminate the strikethrough. Never got a follow-up so don't know if OP got fixed up or not. Gord Dibben Excel MVP On Fri, 25 Jun 2004 08:49:34 -0...

Bug in receiving entry. error calculating Prev Qty Shipped
Dynamics GP Bug Report, version 9.0 service pack 1. Version Information: 9.00259 Location: Receiving Transaction Entry Description: As one enters receiving transactions lines and adjusts the Qty Shipped, the quantity previously shipped is calculated incorrectly. This PO transaction was for a quantity of 120. A quantity of 15 were received on a previous shipment. The Qty previously shipped should display 120-15-30=75 not 45. To make this error occur continue to change the Qty Shipped. Sometimes the error will occur quickly, other times it might take 25 changes. Products Load...

Budget Bug
Does anyone know when Microsoft is going to fix the bug in the budget planner in regard to credit cards and transfers. My budget gets totally wacking when I add credit cards to debt planner. It looks like it includes all the balances in the budget uder special -> debt. I have tried everything I can thing of to stop it. Iis it possible to set a credit card account up and have it paid in the budget under that category. Don't tell me to check the check boxes in account details. That does not work!!!!!!!!!!! When you make a payment it treats it has a transfer to the credit card account. So...

If statement referring to a blank cell
I want to put an if statement in code that will say something like If a1 is blank then blah blah blah end if I can not seem to figure out how to refer to "blank" papa Sub blank() If Range(A1").Value = "" Then MsgBox "blah, blah, blah" Else: MsgBox "halb, halb, halb" End If End Sub Gord Dibben Excel MVP On 16 Mar 2005 15:28:11 -0800, "papa jonah" <adullam04-excelgoogle@yahoo.com> wrote: >I want to put an if statement in code that will say something like > >If a1 is blank then > >blah blah blah > &...

Using concatenate and Indirect to reference a range from a remote workbook
Hi all I am trying to compare values in two ranges, one from the active workbook and the other from a different remote workbook. I am using the Vlookup with the table array field being populated from a range of concatenated cells. The first one defines the path of the remote workbook, the second defines the name of the workbook (because the workbook name changes each week) and the third is the range name. So the Table array part of the Vlookup looks like ths:- INDIRECT(CONCATENATE(File_path,"Forecast_Chk_WK_",Cur_Week-1,".xls!FCast_tot")) Now prior to adding the File_pa...

Cell Reference question
This is probably a simple question, but I have never seen it before. I have been looking at some spredsheets sent by some co-workers, trying to figure out their structure etc... and I have seen some cell references where the reference starts with an =+'sheetname!e2 The + (plus sign) struck me as odd, I can't recall seeing that before. Is there some significance to it? Thanks Tom Believe it's a "legacy" behaviour for die-hard Lotus converts <g> The "+" is not necessary .. -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot&g...

Dynamic references in diagram source data?
Have encountered problems when copying and pasteing diagrams and their source data areas. I wish to exclude the $ signs in the source data formulas in order for the diagrams to retrieve data from different areas in the same spreadsheet. This however seems impossible because excel automatically reinsert the $ signs when I manually remove them from the source data formulas. Any thoughts? Thanks. -- Olzki ...

Cell References: R[-2]C[0] vs A10
One of my excel spreadsheet suddenly replaced all of the "normal" Ax references to R[x]C[y] references. How to I restore the default reference system??? Thanks In Excel 2007: Office button>Excel Options>Formulas>Working with formulas>uncheck R1C1 reference style>OK Other versions of Excel: Tools>Options>General tab>Settings>uncheck R1C1 reference style>OK -- Biff Microsoft Excel MVP "Arthur" <Arthur@OregonKoiGardens.com> wrote in message news:D237D1FE-AC5B-40EC-B535-32EC8121F8EA@microsoft.com... > One of my excel spreads...

Bug in payroll arrearage transaction screen GP 9.0
We got an error that locks up GP. To get the error navigate to CARDS >> PAYROLL >> ARREARS TRANSACTIONS. Select an employee that has a fully collected arrearage. Click the ALL option for View:. Now on a transaction with a zero balance, select the date and try to change the date, either by typing or dropping down the calendar and selecting another date. Once you do that, you cannot get the focus off of that field, even if you put the date back to the original. The only fix appears to be end GP with the task manager or reboot. I know there is no reason to need to change the da...

character reference letter
I need some ideas on how to go about typing a character reference letter. The templates here are mostly for Word. http://office.microsoft.com/en-us/templates/results.aspx?qu=reference&av=TPL000 -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "charater reference letter" <charater reference letter@discussions.microsoft.com> wrote in message news:19364538-CFE3-47F9-A9D7-E3E9591847AE@microsoft.com... >I need some ideas on how to go about typing a character reference letter. There are lots of websites out avail...

How to Get Reference of CommandBar in Excel?
hi! i am using VC7 and Office 2003. I want to add a toolbar in EXCEL with some buttons. i have done that for word already. First i get the reference of the CommandBar using the following code. HRESULT hr; CComPtr<Office2003::_CommandBars> spCmdBars; //QI() for application. CComQIPtr<MSWord::_Application> spWordApp(m_pApplication); ATLASSERT(spWordApp); m_pWordApp = spWordApp; hr = m_pWordApp->get_CommandBars(&spCmdBars); so now when i try to do the same with Excel i receive an error message that get_CommandBars is not member of m_pExcelApp. i don't know how to...

Symbols/Icons Reference for SO-POS
Anyone know where I could find a listing of all the different symbols/icons that can be displayed in Store Operations-POS and what each of them mean? I've seen a few now.. and have no idea what they mean. There isn't a listing of them in the booklet. At the moment we have one that is composed of horizontal lines, 2 blue lines and1 red line. It doesn't go away when we restart POS either, but it will if we restart the whole computer. Anyway, a reference would be nice... Thanks. That description doesn't ring a bell but try running POS, F1 Help, Contents, POS program, M...

Excel BUG in macro recording (every version)
I have found a bug in every Excel version, where can i post it? 1) Start recording a new Macro 2) Go to Print Preview 3) Adjust a margin 4) Stop recording The macro contains errors because it has empty parameters for the margins: With ActiveSheet.PageSetup .LeftMargin = Application.InchesToPoints(0.84) .RightMargin = Application.InchesToPoints() .TopMargin = Application.InchesToPoints() .BottomMargin = Application.InchesToPoints() .HeaderMargin = Application.InchesToPoints() .FooterMargin = Application.InchesToPoints() w From XL2003 ..L...