circular files; iterating to a "correct" solution

I am working with a circular file.  I have been making changes to the file, 
and I have noticed at times when i manually calculate that the file iterates 
to an obviously "incorrect" solution.

No amount of changing the max no of iterations or the max change seems to 
fix the problem.  It iterates to the same "incorrect" solution every time.  
It is like it is stuck in a rut and can't get out.

I have also noticed that, if I manually calculate each worksheet separately 
(that is, hit shift - f9 simulaneously on each worksheet individually, from 
left to right) all the worksheets seem to calculate correctly.  This leads me 
to believe that the file can calculate perfectly fine, and that the issue is 
with how excel iterates when it is asked to "globally" iterate (when I just 
hit f9 and let excel do its thing).

Am I doing something wrong here?  Any know how to fix this?

Regards,
Jeff
0
12/8/2005 3:38:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
224 Views

Similar Articles

[PageSpeed] 15

What is your formula? What are the input values? What result did you expect
and what did you get instead?

Kind regards,

Niek Otten

"jefftlewis" <jefftlewis@discussions.microsoft.com> wrote in message 
news:0E0F8E90-2094-4BFB-A258-070D0413679F@microsoft.com...
>
> I am working with a circular file.  I have been making changes to the 
> file,
> and I have noticed at times when i manually calculate that the file 
> iterates
> to an obviously "incorrect" solution.
>
> No amount of changing the max no of iterations or the max change seems to
> fix the problem.  It iterates to the same "incorrect" solution every time.
> It is like it is stuck in a rut and can't get out.
>
> I have also noticed that, if I manually calculate each worksheet 
> separately
> (that is, hit shift - f9 simulaneously on each worksheet individually, 
> from
> left to right) all the worksheets seem to calculate correctly.  This leads 
> me
> to believe that the file can calculate perfectly fine, and that the issue 
> is
> with how excel iterates when it is asked to "globally" iterate (when I 
> just
> hit f9 and let excel do its thing).
>
> Am I doing something wrong here?  Any know how to fix this?
>
> Regards,
> Jeff 


0
nicolaus (2022)
12/8/2005 7:35:33 AM
Niek:

Thank you for your reply.  It is a fairly involved file, calculating 
financial statements for a proposed project.  Have you heard of the 
phenomenon I describe below?  I was wondering if there was a way to somehow 
add some flexibility to the iteration process.  (I did not create this file 
and I hate circular files, I am just stuck with this crummy intentionally 
circular file.  It would take a ton of effort to rebuilt it and clear it of 
the circularity.)

Regards,
Jeff

"Niek Otten" wrote:

> What is your formula? What are the input values? What result did you expect
> and what did you get instead?
> 
> Kind regards,
> 
> Niek Otten
> 
> "jefftlewis" <jefftlewis@discussions.microsoft.com> wrote in message 
> news:0E0F8E90-2094-4BFB-A258-070D0413679F@microsoft.com...
> >
> > I am working with a circular file.  I have been making changes to the 
> > file,
> > and I have noticed at times when i manually calculate that the file 
> > iterates
> > to an obviously "incorrect" solution.
> >
> > No amount of changing the max no of iterations or the max change seems to
> > fix the problem.  It iterates to the same "incorrect" solution every time.
> > It is like it is stuck in a rut and can't get out.
> >
> > I have also noticed that, if I manually calculate each worksheet 
> > separately
> > (that is, hit shift - f9 simulaneously on each worksheet individually, 
> > from
> > left to right) all the worksheets seem to calculate correctly.  This leads 
> > me
> > to believe that the file can calculate perfectly fine, and that the issue 
> > is
> > with how excel iterates when it is asked to "globally" iterate (when I 
> > just
> > hit f9 and let excel do its thing).
> >
> > Am I doing something wrong here?  Any know how to fix this?
> >
> > Regards,
> > Jeff 
> 
> 
> 
0
12/8/2005 12:56:02 PM
Hi Jeff,

It depends on the functions/formulas.
Personally I would never rely on circular references for iteration; I always 
use Goal seek (or, for more complex operations, Solver).
If the function has one or more maximum/minimum the value you start with is 
important; iteration will not get you to the other side of a top or bottom.
All that can easily be controlled with Goal seek.
However, if you know a bit about the logic used, you may be able to estimate 
a reasonable start value for some of the variables and so influence the 
route of iteration.

-- 
Kind regards,

Niek Otten

"jefftlewis" <jefftlewis@discussions.microsoft.com> wrote in message 
news:D78B7DAF-9444-4A1D-98F2-7041E24404D5@microsoft.com...
> Niek:
>
> Thank you for your reply.  It is a fairly involved file, calculating
> financial statements for a proposed project.  Have you heard of the
> phenomenon I describe below?  I was wondering if there was a way to 
> somehow
> add some flexibility to the iteration process.  (I did not create this 
> file
> and I hate circular files, I am just stuck with this crummy intentionally
> circular file.  It would take a ton of effort to rebuilt it and clear it 
> of
> the circularity.)
>
> Regards,
> Jeff
>
> "Niek Otten" wrote:
>
>> What is your formula? What are the input values? What result did you 
>> expect
>> and what did you get instead?
>>
>> Kind regards,
>>
>> Niek Otten
>>
>> "jefftlewis" <jefftlewis@discussions.microsoft.com> wrote in message
>> news:0E0F8E90-2094-4BFB-A258-070D0413679F@microsoft.com...
>> >
>> > I am working with a circular file.  I have been making changes to the
>> > file,
>> > and I have noticed at times when i manually calculate that the file
>> > iterates
>> > to an obviously "incorrect" solution.
>> >
>> > No amount of changing the max no of iterations or the max change seems 
>> > to
>> > fix the problem.  It iterates to the same "incorrect" solution every 
>> > time.
>> > It is like it is stuck in a rut and can't get out.
>> >
>> > I have also noticed that, if I manually calculate each worksheet
>> > separately
>> > (that is, hit shift - f9 simulaneously on each worksheet individually,
>> > from
>> > left to right) all the worksheets seem to calculate correctly.  This 
>> > leads
>> > me
>> > to believe that the file can calculate perfectly fine, and that the 
>> > issue
>> > is
>> > with how excel iterates when it is asked to "globally" iterate (when I
>> > just
>> > hit f9 and let excel do its thing).
>> >
>> > Am I doing something wrong here?  Any know how to fix this?
>> >
>> > Regards,
>> > Jeff
>>
>>
>> 


0
nicolaus (2022)
12/8/2005 1:13:31 PM
Ok, thanks.

Yeah I don't understand the model real well.  I'm going to ask the creator 
for help.  Perhaps he can suggest what inputs to modify.

The frustrating thing is I am not sure what changes that I am making to the 
model are triggering this failure to iterate.  And once the model can't 
iterate, the file is garbage basically.  (I have to revert back to a 
previously saved version, and continue working with that.)

So I consider this another fundamental problem with the use of circular 
excel files (or circular excel models created by someone other than you, in 
which the circularity is not obvious to you).

Thanks, Jeff


"Niek Otten" wrote:

> Hi Jeff,
> 
> It depends on the functions/formulas.
> Personally I would never rely on circular references for iteration; I always 
> use Goal seek (or, for more complex operations, Solver).
> If the function has one or more maximum/minimum the value you start with is 
> important; iteration will not get you to the other side of a top or bottom.
> All that can easily be controlled with Goal seek.
> However, if you know a bit about the logic used, you may be able to estimate 
> a reasonable start value for some of the variables and so influence the 
> route of iteration.
> 
> -- 
> Kind regards,
> 
> Niek Otten
> 
> "jefftlewis" <jefftlewis@discussions.microsoft.com> wrote in message 
> news:D78B7DAF-9444-4A1D-98F2-7041E24404D5@microsoft.com...
> > Niek:
> >
> > Thank you for your reply.  It is a fairly involved file, calculating
> > financial statements for a proposed project.  Have you heard of the
> > phenomenon I describe below?  I was wondering if there was a way to 
> > somehow
> > add some flexibility to the iteration process.  (I did not create this 
> > file
> > and I hate circular files, I am just stuck with this crummy intentionally
> > circular file.  It would take a ton of effort to rebuilt it and clear it 
> > of
> > the circularity.)
> >
> > Regards,
> > Jeff
> >
> > "Niek Otten" wrote:
> >
> >> What is your formula? What are the input values? What result did you 
> >> expect
> >> and what did you get instead?
> >>
> >> Kind regards,
> >>
> >> Niek Otten
> >>
> >> "jefftlewis" <jefftlewis@discussions.microsoft.com> wrote in message
> >> news:0E0F8E90-2094-4BFB-A258-070D0413679F@microsoft.com...
> >> >
> >> > I am working with a circular file.  I have been making changes to the
> >> > file,
> >> > and I have noticed at times when i manually calculate that the file
> >> > iterates
> >> > to an obviously "incorrect" solution.
> >> >
> >> > No amount of changing the max no of iterations or the max change seems 
> >> > to
> >> > fix the problem.  It iterates to the same "incorrect" solution every 
> >> > time.
> >> > It is like it is stuck in a rut and can't get out.
> >> >
> >> > I have also noticed that, if I manually calculate each worksheet
> >> > separately
> >> > (that is, hit shift - f9 simulaneously on each worksheet individually,
> >> > from
> >> > left to right) all the worksheets seem to calculate correctly.  This 
> >> > leads
> >> > me
> >> > to believe that the file can calculate perfectly fine, and that the 
> >> > issue
> >> > is
> >> > with how excel iterates when it is asked to "globally" iterate (when I
> >> > just
> >> > hit f9 and let excel do its thing).
> >> >
> >> > Am I doing something wrong here?  Any know how to fix this?
> >> >
> >> > Regards,
> >> > Jeff
> >>
> >>
> >> 
> 
> 
> 
0
12/8/2005 5:18:02 PM
Reply:

Similar Artilces:

Price from database does not showup correctly in POS
When I try to ring up items in the POS the sale price of some items does not showup correctly in POS. It shows a totally different sale price which is sometimes less or sometimes more than the sale price in the database. I always bachup my database and also refresh the Sql server but the problem continues. Does anybody has a solution to this. Regards, Nitin Try reindexing the DB in SO Administrator - Database/Reindex Other than that, can you provide more details? Have you set up customers with Discount Percentages or Price Levels? -- Glenn Adams Tiber Creek Consulting http://www.t...

OWA Premium Client Not Displaying Correctly
Hello, I wonder whether anyone has seen this problem before because i would really appreciate any help on this one please. I have six Exchange 2003 servers in my organisation and there are two Exchange servers in three sites. Four of the Exchange servers are running Windows Server 2003 and Exchange Server 2003 [With Exchange SP1]. The front-end server is running Windows Server 2003 SP1 and Exchange Server 2003 SP2. Users with mailboxes on those four Back-End Exchange servers do not have any problems using OWA. The two Exchange servers on my site are using Windows Server 2003 SP1 with Exc...

Drop Shadows Not Printing Correctly
For the past several years I have been using Photoshop to creat graphics with drop shadows. I recently tried to use them with Publsiher 2003 and instead of the shadow gently fading to white, there is a shadow with a vivid stopping point in Publsher when I print. On the screen it looks OK. I must have somenting set wrong as all my other programs print it correctly. (Open Office Writer, Photoshop, Adobe InDesign CS - I have not tried MS Word.) I have used different file formats, but still nothing prints correctly. Any help you can give me would be appreciated. funzero <funzer...

Install the corrective update for Internet Explorer
--ioxgjdwmtoicrdnc Content-Type: multipart/related; boundary="mpbqztfn"; type="multipart/alternative" --mpbqztfn Content-Type: multipart/alternative; boundary="cwjmmabuowvnuf" --cwjmmabuowvnuf Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to protect ...

Publisher 2003 word box not working correctly
I have MS Office 2003. When I insert a word box it will no longer let me stretch the box to the desired size. When I try it just move the whole box. ...

correction to my last post
i meant everything was retained including my files ...

Check this correction package
--zhrncaydvppbupomk Content-Type: multipart/related; boundary="gsvkdwntdacbwbfcp"; type="multipart/alternative" --gsvkdwntdacbwbfcp Content-Type: multipart/alternative; boundary="oqejjsfk" --oqejjsfk Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now...

Access 2007 What is up with correcting my spelling when I put data in a field?
Why on earth would a database (storage device) ever want to correct the spelling of something I put into it? Here is my problem. I created a table with a field as a test for someone and in one of the rows I entered a field with the word Cafe When I queried the database to find all instances of the word 'Cafe' I found 0 results. When I went back into the table to see what was going on, I discovered that the data in the field was change to Caf� This to me makes no sense. And even if I can turn off spell checking, why would anyone change the information I'm telling it to sto...

Install this correction update
--lbcrcuzyjmblgniy Content-Type: multipart/related; boundary="dqvgcmmlxjz"; type="multipart/alternative" --dqvgcmmlxjz Content-Type: multipart/alternative; boundary="dvydzshbavws" --dvydzshbavws Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now ...

vat adds up correctly accross spreadsheet but down the columns whe
I have a very basic spread sheet which adds VAT to various cost figures. I have a total VAT cell below the VAT columns. When adding the columns together the answer is normally out by 1p less. Example: Cost VAT £317.00 55.48 £317.00 55.48 Total VAT should be £110.96 but Excel calculates at £110.95 The above calculation may be a bad example but the above occurs when the VAT is rounded up or down against an individual cost figure. So why is the Total different if the sum is just the adding up of the VAT individual totals? Thanks because 17.5% ...

Last chart not formatting correctly when scroll bar not at bottom
Hi All! I have a macro that runs through however many worksheets a workbook has and charts the data from each worksheet into a final "charts" worksheet. I have about 6 or 7 charts showing, but you can scroll through all 40 if you wish. When it runs it always finishes with the scroll bar at the bottom, showing the last group of charts. If you then scroll up to view any of the upper charts, then re-run the macro, the very last chart does not finish formatting. Then if you scroll up or down, it goes away but it is correctly formatted at the bottom of the list (where it belongs). It...

W2's being correct without numbers differeing due to inactivation
It appears that the W2's do not print correctly if the employee has been inactivated due to termination throughout the year. Termination also effects garnishments and whether they print in the monthly report. I also wish if it is a weekly deduction such as payroll if the employee does not work that week it would carry that amount forward to deduct fromt he next paycheck. It appears that payroll is the weakest module. Is this true? Or is this a story from our controller that just doesn't want to do his job. ---------------- This post is a suggestion for Microsoft, and Micro...

FW: Look at that corrective update from MS Corp.
--icpiucamxqhgt Content-Type: multipart/related; boundary="ndwmbdwziozf"; type="multipart/alternative" --ndwmbdwziozf Content-Type: multipart/alternative; boundary="rqdmcrwevlupw" --rqdmcrwevlupw Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to con...

Newbie Help! Please (correct version)
Hi I have a spreadsheet which has a record of places in which certain occurences have taken place looking somthing like this... �-----A-----�-----B-----�-----C-----�-----D----- Place 1 data data data Place 2 data data data Place 3 data data data Place 1 data data data Place 4 data data data Place 2 data data data Place 1 data data data Pl...

KB 955452
Hi, after applying CRM 4.0 Rollup 2 I tried to manually enable this hotfix but the description in KB article http://support.microsoft.com/kb/955452/ is quite unclear to me. Can anybody help me how to set a registry key correctly? Thank you Jan Vanek I agree Can someone please explain the last bits of the registry key Pete "Jan Vanek" <JanVanek@discussions.microsoft.com> wrote in message news:1548A89A-AD39-4B8F-8944-1ABB24ADE20F@microsoft.com... > Hi, > after applying CRM 4.0 Rollup 2 I tried to manually enable this hotfix but > the description in KB article htt...

correct use of "default" attribute in XML Schema
Hi all, what is the correct use of the "default" attribute in XML Schema? For example: <xs:element name="myProperty" type="xs:string" default="myDefaultValue"/> What can I do with it? What is the meaning of < ....default="myDefaultValue" /> ? Thank you so much. Bye -- Stefano Giannone MCP .NET http://www.giasoft.it Default means there is an application that preempts possible words in xml. It will expect alignment to BIG MONEY "standard" way and methods and will not give a "notch". For xml "US...

issue with Workday Function
From: amran.majid@gmail.com Newsgroups: microsoft.public.excel Subject: issue with Workday Function - not calculating workdays correctly! Date: Thu, 05 Oct 2006 12:23:03 -0000 hi, trying to use the workday function to calculate the number of workdays from a specific date. i am trying to calculate the 11th working day for each month. it works for October, but not november and december 2006. A1 = 01/10/2006 B1 = 02/11/2006 C1 = 03/12/2006 A2 = WORKDAY(A1,11,Holidays) returns a value of 16/10/2006 which is correct. B2 = WORKDAY(A1,11,Holidays) returns a value of 16/11/2006 which is incorrec...

Auto correct and capitalization otlook 2003
Hi there, I am having a wierd issue with the Auto Capitalization in outlook 2003. I am using word as the email editor, and have "capitalize first letter of sentences" enabled under tools-options-spelling-auto correct. I have tried with rich text, plain text and html and it is just not capitalizing. The ONLY time i can get it to capitalize is if i diable " use word as editor" and select rich text, but this does not underline spelling and grammar so is not an option. I have installed all the service packs and updates. Repaired and re-installed office. It works flawlessly o...

Which service-start-type is correct to register a non-PnP device service.
Dear everyone: I want my app to get a handle of a service for a non-PnP driver like a memory I/O on windows. The service is registerd SERVICE_AUTO_START in the device manager and my app is in the StartUp program. Sometimes, my app doesn't get a handle at first but after several trial in loop, that's OK. I'd like to know what's wrong. The handle is as follows. hDevice = CreateFile("\\\\.\\SERVICE",GENERIC_READ | GENERIC_WRITE,0,NULL,OPEN_EXISTING,FILE_ATTRIBUTE_NORMAL, NULL); Sincerely Yours YoungHun Kim You don't do CreateFile on a service; you do Cre...

Using an if, have to correct when customer closes out
I right now have this: =IF(MONTH(D1)=MONTH(CFBB1!H394),CFBB1!A394,IF(MONTH(D1)=MONTH(CFBB1!H7),CFBB1!A7,IF(MONTH(D1)=MONTH(CFBB1!H30),CFBB1!A30,IF(MONTH(D1)=MONTH(CFBB1!H61),CFBB1!A61,IF(MONTH(D1)=MONTH(CFBB1!H95),CFBB1!A95,IF(MONTH(D1)=MONTH(CFBB1!H128),CFBB1!A128,CFBB1!A704)))))) This is for customers who are going to be comig due. If a customer closes out their account, then the customers reference will no longer be valid. Is there any way to have this auto correct? --- Message posted from http://www.ExcelForum.com/ Hi what do you mean with 'reference won't be valid'. Wi...

Getting back to the correct main identity
Hello, I purchased a new MacBook Pro and migrated all the files from the old computer to the new one. When I open entourage, it has a new main identity and none of the old emails I've sent in the past. How do I switch to the old identity I was using before? Thanks, Jo Ann On 1/28/10 12:49 PM, in article C7873870.198B7%wildfarms@earthlink.net, "Wild Farm Alliance" <wildfarms@earthlink.net> wrote: > I purchased a new MacBook Pro and migrated all the files from the old > computer to the new one. When I open entourage, it has a new main identity > a...

Problem with Excel getting correct # of records back from Access
I have a qry in MSAccess that brings me 1316 rows, but when I go to excel and run the qry it only gives me 692 rows. It look like it isn't making it to the 2nd statement or the "Or" statement in the query. Looking at the data in Excel. I can't remember how to get this to pass all the records over to Excel. Here's my "Having" part in the Qry: HAVING ((([Tracking].[Type of Case])="Expedited" Or ([Tracking].[Type of Case])="Fast Track" Or ([Tracking].[Type of Case])="Standard Pre-Service") AND (([Tracking].[Appeal or Gr...

HOW TO PASS THE VALUE TO CORRECT INPUT BOX ?
Hi, I am a new member in this group.. I have some dificulties in form's date input box. I am trying to get date from a calendar and there are two date input box. How do I pass the dates into respective input boxs. How do I check the cursor whether is in Textbox1 or Textbox2 ? Not quite clear on this. When a control is invoked (entered), the events for that control fire. So normally you would add code to the events for the specific control. So, the user would type into Textbox1 say, and you could code in the Change, Exit events, or any of the other events associated with ...

BUG: Charts are drawn in correctly in right-to-left spreadsheets
Good day! In beta 2, charts are drawn incorrectly when embedded in right-to-left sheets (when the A column is displayed on the right side, for example when using Hebrew). This bug existed also in Beta-1, and unfortunately was not fixed in beta-2. Thanks, Yuval ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then ...

Try on these corrective package
--mwqmihkvqujukt Content-Type: multipart/related; boundary="pxdwufjm"; type="multipart/alternative" --pxdwufjm Content-Type: multipart/alternative; boundary="pfcuozwiqw" --pfcuozwiqw Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "November 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to continue keeping your computer secure from these vulnerabilities, the m...