Convergence chart ¿Is this imposible in excel?

S. García

unread,

Mar 17, 2002, 4:24:15 PM3/17/02

to

Hello,
I am solving a simple problem using manual calculations and 1 iteration each
time I press F9.
I would like to store the solution value for each iteration and then make a
graphic showing
how the values were changing until reach the solution of the problem, (a
convergence graphic).

My question is that I do not find the way to store the old values of the
solution, and every
time I press F9 I lost the last value. I have tried to define a group of
sentences like these
in a range of cells:

=if(C9=2;C10)
=if(C9=3;C10)
=if(C9=4;C10)
...........

where C9 i s the cell which has the iteration number and C10 is the cell
with the solution,
my intention was to store in the cell the value of the solution in the
iteration 2, 3 etc.
but the problem is that when the iteration is not 2, 3 or 4 Excel returns
"FALSE" instead
leaving inaltered the past value of the solution.

¿Some ideas for this?
¿Any way to leave unchanged the value if the condition is not fullfiled?
¿Any other way to do that?
(I got an answer for this in other news list but i do not manage with Visula
Basic on EXcel)
Thanks in advance

Santiago Garcia

Bernard Liengme

unread,

Mar 17, 2002, 6:24:53 PM3/17/02

to S. García

Hello Santiago,
a) Make a macro and let VBA fill up a range
or b) if there are not too many iteration:
after each iteration copy cell (C9); move to position in you table of data and
use Edit|PasteSpecial Values

Best wishes
Bernard

Bernard Liengme

unread,

Mar 17, 2002, 6:24:25 PM3/17/02

to S. García

Hello Santiago,
a) Make a macro and let VBA fill up a range
or b) if there are not too many iteration:

after each interation; copy cell (C9); move to position in you table of data and
use Edit|PasteSpecial Values

Best wishes
Bernard

Tushar Mehta

unread,

Mar 19, 2002, 9:13:34 PM3/19/02

to

An alternative to the straightforward methods proposed by Bernard is an
iterative solution.

First, set the calculation options (Tools | Options... | Calculation
tab): check the Interation checkbox and set the Maximum Iterations to 1.

To use iteration for an effective solution, we need a 'trigger' to reset
calculations. Use B1 for that. B1 will contain TRUE to reset the
iterative solution, and FALSE to find the next iterative value.

In B2, enter the starting value for the solution. This is what we'll
use when we 'reset' the calculations.

In A4, enter the formula =IF(B1,0,A4+1). This counts the sequence
number in the iterative solution.

In B4, enter the formula =IF(B1,$B$2,B4-B4/(B4+1)). If B1 (the reset
trigger) is true, the value will equal the starting value. If not
(i.e., the false part of the IF statement) calculates the next value in
the iteration sequence. Use the formula appropriate to your own need.

In B5, enter the formula =IF($A$4>=ROW()-4,IF(ISNA(B5),$B$4,B5),NA())
Copy this formula down from B5 to as many iteration values that you
want. These 'save' successive values of the iterative process.

Plot B5:B<whatever>.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel

In article <3c950972$1...@filemon.telecable.es>, esqui...@yahoo.es
says...

S. García

unread,

Mar 20, 2002, 4:08:21 PM3/20/02

to

Thanks very much for your ideas Bernard and Tushar.
I think it will work now
Santiago

"S. García" <esqui...@yahoo.es> escribió en el mensaje
news:3c950972$1...@filemon.telecable.es...