Using iteration to solve a two-parameter equation

[Jason]

Hi everyone,

My goal: My spreadsheet has two inputs and two outputs. I input the
initial values -magic calculations happen- and out come my two outputs,
which are meant to be my new inputs. This process needs to happen
until my outputs match the previous inputs to a degree that I specify,
convergence. Cookie cutter Excel iteration issue?

My problem: If I just make my inputs refer to the same cells as my
outputs, and turn iteration on, I never reached convergence and I end
up with some kind of error.

My pseudo-solution: I wrote a macro that copies the values of the
output to the inputs and after running it about 8 times, I am able to
converge (inputs=outputs)

I would like to use Excel's iteration for this instead of a macro. I
suspect the problem has to do with the timing of the calculations, but
I really dont know. Help is greatly appreciated

Have a good day

Jason

 

[Niek Otten]

Hi Jason,

What is "some kind of error"?

 

[Guest]

Hi,

Can you please elaborate a little more on what those calculations are?
Depending on the complexity of the equations involved, the convergence will
depend critically on the intitial guess values that you input for the two
parameters; it is also possible that different intitial guess values may
converge but return different outputs (yet satisfying the convergence
criteria).

Try one of the following:

Enter the input values in A1 and B1; and the formulas for calculating the
output values in C1 and D1 (obviously these formulas would refer to A1 and
B1).
Now use the following formulas:
In A2, =C1
In B2, =D1
Drag the formulas in C1 and D1 to C2 and D2.
Now you have the second row, A2,.....D2, filled.
Drag the formulas in the second row (i.e., A22) down the rows.
If the parameters are converging, Ai and Ci (and similarly Bi and Di) should
eventually become equal, where 'i' is row numbers down the spreadsheet.
See how changing the input values in A1 and B1 affect the convergence.

OR

Use the 'Solver' utility in Excel:

If A1 and B1 contain the input values, and C1 and D1 contain the output
values,
create helper cells E1 and F1 with formulas =A1-C1 and B1-D1 repsectively.
In Solver,
Set Targe Cell to E1,
By Changing, A1:B1
Add a Constraint F1 = 0
and solve. Again, the initial guess values in A1 and B1 are very critical.
The system may not converge at all or converge to weird values.
Test with different initial guess values.

Regards,
B. R. Ramachandran

 

[Jason]

Thanks for reading my posting Niek!

I eventually get a #NUM error in all of the cells involved in the
calculation.

Jason

 

[Jason]

Thank you for the suggestions B.R.

First of all, the calculations are fairly complex. The only way that I
can describe them generally is to say that they have the form of a
matrix equation and the solution is the adjustments on the two initial
values. These values are added to the initial values (input) to yield
the final values (output).

I am having trouble adapting your first suggestion to my calculations,
but I will keep trying.

Solver failed to converge using any of the values that I tried. I
understand that the initial estimates can have an impact on
convergence, but I am simply trying to replicate a calculation that has
been demonstrated in literature repeatedly. Therefore, I have reason
to believe the initial values that I have chosen are adequate.

Do you have any idea what the difference is between the macro and
excel's iteration?

thanks for your help