Iteration within iteration

Hi all,

I am trying to make an iterations within an iterations, create a command button on a userform to activate/call it, and display on said userform. The steps I am thinking of are:

1. Make initial guess of Value 1 and (FG0). Guess the value of FG0, then the result would be bgi1.
2. With bgi1, I calculated the value of bgi2. The difference between bgi1 and bgi2 shall be 5%.
3. If not, I have to guess the new bgi1. The second value of bgi1 is independent of Fg0.
4. Once bgi1 and bgi2 difference is around 5%, I proceed with the rest of the calculation and get FG1. Difference between FG0 and FG1 shall be 5%.
5. If the difference is bigger than 5%, I have to guess another new FG0, and repeat Step 1 - 4

I was thinking of using Solver, but sometimes I do not get the consistent working answer. Sometimes I get the values, but if the initial guess of FG0 is 0 or TOO BIG, I just get DIV/0! error.

Another problem being, the Solver Tab and the Display tab is different. Means when I open the userform, the that I want to be visible is not the one with all the equations. Therefore I cannot get my Solver function to work. I need to have its tabs active. Is there a workaround to this?

Please find my sample file attached. I have assigned a Solver function as well if you want to have a look at it.

Thank you.

Cross Post from here: http://www.ozgrid.com/forum/forumdisplay.php?f=158

crossposted: http://www.ozgrid.com/forum/showthre...d=1#post780436

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

I would have to see a more complete worked example. I don't see how iteration even fits into the algorithm you describe.

You talk about using bgi1 as an initial value, compute bgi2 from bgi1, then put the value for bgi2 into bgi1. bgi2 is not dependent on bgi1 (unless bgi1<(bgt=20)). For any value greater than 20, bgi2 will not change. if you want a value for bgi1 that is 95% of bgi2, then compute bgi2 from bgt, then multiply bgi2 by 0.95 to get bgi1 -- no iteration needed. Similar for fg0 to fg1. fg1 is not dependent on fg0, so there is really nothing to iterate on. As for whether or not to use Solver, I don't see a function in Front Page!C23, so I don't know what your objective function should be, which means it is difficult for me to comment on how well Solver will solve that function. I think that, if I am going to be able to help at all, I need a better understanding of the function to be solved and the algorithm that you want to use to solve it.

At this point in the development, I tend to do something like illustrated in the attachment. Note that I have added several columns to iteration example. With these additional columns, I will make copies of the main calculation column (column C). Then I will link the desired values in column D to column C (see the function in D11, where I simply make bgi1 in column D equal to bgi2 in column C). When I see the bgi1-bgi2 "loop" converge, then I can see what happens as I change fg0 (see E7). As I describe above, there is no real connection between bgi2 and bgi1, so only one iteration is needed for this inner loop to converge, and fg0 is not connected to fg1, so only one iteration is needed for this loop to converge.

Note that adding these columns is not intended as a final solution. As part of the development phase of a project like this, putting each iteration into its own column/row is an opportunity to analyze how the chosen algorithm converges and make further decisions about how to approach the problem. You can see how I used this strategy in this thread to analyze a simple successive approximations algorithm I was working on: http://www.excelforum.com/excel-form...ate-event.html (see rows 22 on in the attachment to post #1. you can also see the circular reference/iteration solution in row 16).

At this point, I would suggest that you more carefully analyze the problem you are trying to solve and the iterative algorithm you think you want to use to solve it. We might be better able to help if we better understand your desired objective function and how the iteration fits into the solution.