Calcular el número de iteraciones en una referencia circular.

Calcular el número de iteraciones en una referencia circular.

En los anteriores post sobre referencias circulares e iteraciones (aquí y aquí), vimos como en algunos casos nos puede interesar crear un modelo con una referencia circular, que a través de varias iteraciones converja en un resultado deseado. Para ello cambiamos los valores en la pestaña de Archivo>;Opciones>;Formulas>: (Nº máximo de iteraciones y valor de cambio).

En esos post, creamos un modelo en el que calculaba el importe que se debía de solicitar en un préstamo para que nos cubriese el principal y los intereses. Comprobamos que el valor del modelo convergía a la 5ª iteración al valor exacto. El procedimiento tenía una serie de pegas, desventajas, o era un poco pedestre, me viene a la cabeza dos cuestiones.

•  ¿Cómo podemos hacer para reinicializar los cálculos iterativos? y 

• ¿Cómo puedo calcular el número de iteraciones que han sido necesarias para llegar a converger?

Reinicializar el modelo y la simulación. 

En cada proceso iterativo Excel toma como punto de partida el valor que en ese momento aparece en la celda y en pantalla, de esa forma, en muchas referencias circulares Excel parece que se vuelve loco y empieza a sumar ya engordar los resultados. Como podemos hacer para reiniciar los contadores de una forma que no sea manual.

Para ello en una celda, por ejemplo, ponemos una marca VERDADER= Si quiero que Excel haga iteraciones y FALSO= si quiero que se pare de iteraciones y ponga el valor a cero. Esos valores lo pongo cada vez que hago click, en el botón de iteración que he vinculado a la celda B4, el valor que toma esta tapado por el botón. Ahora solo falta establecer un vínculo entre la celda “marca” y las celdas que tiene referencias circulares, en este caso B5 y B8, en ellas añadimos a la formula circular anterior, un condicional de SI( el valor de B4 es VERDADERO; --> que realice el cálculo circular e iterativo; si es FALSO entonces, que ponga un 0. Así de fácil se consigue reiniciar los valores de este modelo, que sea circulares e iterativos.
 

Gestión de cuenta de iteración 

Para calcular el número de iteraciones utilizamos otra referencia circular, en la que añadimos 1 al valor anterior de la celda, de forma que cuando Excel llegue a la convergencia, deje de añadir uno y el resultado aparezca en la celda. El caso es que si pones una adición de 1, no se rompe la convergencia, y se llegara al criterio de máximo número de iteraciones que en nuestro caso es de 100.
Vale, ¿cómo lo hacemos? El truco es simplemente contar las iteraciones en las mismas unidades que tenemos la opción del valor máximo cambio, en este caso 0,000001, por los que en cada iteración nuestro modelo solo añadirá una millonésima.

De esta forma al pulsar en el botón “Iteraciones”, el modelo se pondrá emn marcha, y la celda B9 tomará el valor de 106,38 euros y en la celda B5 veremos que ha sido necesario 7 iteraciones.

Como otras veces puedes descargarte el fichero ejemplo desde wannanaotes, y esta vez gratis.

A propósito el próximo 26 de marzo del 2013, será el segundo dia del año en el que en todos los pueblos de España se trabaja, pues no hay ningún día de fiesta. Y eso es novedad, pues si, ya que sólo hay 3 días al año en que se produce esta situación. Puedes verlo aquí.