Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
circular files; iterating to a "correct" solution
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
circular files; iterating to a "correct" solution
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
circular files; iterating to a "correct" solution
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" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
circular files; iterating to a "correct" solution
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i forget my Microsoft Excel files password tell me the solution | Excel Worksheet Functions | |||
i forget my Microsoft Excel files password tell me the solution | Excel Worksheet Functions | |||
Recently Used File List - 2002 Contains 'Temp' Files | Excel Discussion (Misc queries) | |||
Generating excel combinations | Excel Discussion (Misc queries) | |||
Cannot access read-only documents. | Excel Discussion (Misc queries) |