Referencias circulares y la configuración del número de iteraciones.

En el artículo titulado “Crear una referencia circular”, veíamos que el modelo no convergía al resultado a la primera, que deberíamos de fijar los valores del “Número de iteraciones” y “Desviación máxima”, que se encontraban en la ficha "Fórmulas" a la que llegavamos através del comando Archivo, y en Opciones de Excel.

Una vez que hemos marcado la casilla "Habilitar cálculo iterativo" Excel realiza varios cálculos iterativos para llegar a la solución de 106,38 €

Para ver cómo funciona a la vez que se activa el cálculo iterativo, y a la vez la opción de cálculo manual, para poder controlar en todo momento el cálculo iterativo. Los valores de número máximo de iteraciones ponlo en 5 y la desviación máxima en 0,001.

Esto significa que Excel realizará hasta un máximo de 5 iteraciones, pero también puede dejar mucho antes, si el valor absoluto de a fórmula no supera el valor de 0,001 (la desviación máxima), y esto lo calcula en cada iteración, iteración a iteración.

¿Cómo se mide la velocidad de la convergencia?

En nuestro pequeño modelo, en la celda B6 contenía la referencia circular, la celda B8 contiene el valor exacto calculado por otro procedimiento, en la celda B9 una equivalencia lógica de igualdad entre ambas celdas y en la B10 la diferencia de los valores de ambas.

Si la diferencia es 0 y el valor de B9 es VERDADERO, tendrás que regenerar la fórmula B6, borrando la formula, recalculando la hoja y escribiéndola de nuevo.

El importe del préstamo es ahora 100 € y la diferencia es de -6,382978723€. Cada vez que pulsamos la tecla [F9] para iniciar el cálculo en cada iteración: B10 toma sucesivamente los valores de  -6,382978723 pasa a -4,9634E-06, a -2,97804E-07, a  -1,78683E-08, a  -1,07211E-09, a -3,86535E-12, a -2,41585E-13 y en la 8ª iteración converge a 0, llegando así al cálculo exacto.

Repaso.

Repasando lo visto hasta ahora, para usar fórmulas de manera eficiente, hay tres cuestiones importantes en las que tiene que pensar y que podemos ver en la ayuda de Excel:

• Cálculo   es el proceso de calcular fórmulas y mostrar después los resultados como valores en las celdas que contienen las fórmulas. Para evitar cálculos innecesarios, Microsoft Excel vuelve a calcular fórmulas automáticamente solo cuando han cambiado las celdas de las que depende la fórmula. Éste es el comportamiento predeterminado cuando abre por primera vez un libro y cuando lo está modificando. Sin embargo, puede controlar cuándo y cómo Excel vuelve a calcular fórmulas. 
• Iteración   es el cálculo repetido de una hoja de cálculo hasta que se cumple una condición numérica específica. Excel no puede calcular automáticamente una fórmula que hace referencia a la celda, ya sea directa o indirectamente, que contiene la fórmula. Esto se denomina referencia circular. Si una fórmula hace referencia a una de sus propias celdas, deberá determinarse cuántas veces hay que actualizar la fórmula. Las referencias circulares se pueden recorrer en iteración de manera indefinida. Sin embargo, puede controlar el número máximo de iteraciones y la cantidad aceptable de cambios. 
• Precisión   es una medida del grado de precisión de un cálculo. Excel almacena números y realiza cálculos con una precisión de 15 dígitos significativos. Sin embargo, puede cambiar la precisión de los cálculos para que Excel utilice los valores mostrados en vez de los valores almacenados al actualizar fórmulas.

Y los atajos del teclado para el recalculo son:

Para	Presione
Volver a calcular las fórmulas que han cambiado desde el último cálculo y las fórmulas que dependen de ellas en todos los libros abiertos. Si un libro está establecido para que se vuelva a calcular automáticamente, no es necesario presionar F9.	F9
Volver a calcular las fórmulas que han cambiado desde el último cálculo y las fórmulas que dependen de ellas en la hoja de cálculo activa.	MAYÚS+F9
Volver a calcular todas las fórmulas de todos los libros abiertos, independientemente de que hayan cambiado o no desde la última vez.	CTRL+ALT+F9
Volver a comprobar las fórmulas dependientes y, a continuación, volver a calcular todas las fórmulas de todos los libros abiertos, independientemente de que hayan cambiado o no desde la última vez.	CTRL+MAYÚS+ALT+F9

Si te descargaste el modelo en el artículo anterior, no hace falta que lo hagas, es el mismo, sino, puedes descargar el modelo que hemos visto, modificarlo y enredar en él, en wannannotes y colaborar con el blog, pulsando aquí.

No te pienses que esto se termina, la forma de calcular las hojas y de las iteraciones da para unos cuanto más artículos, este ha sido inspirado en Thiriez. Así que hasta la próxima de excel.