Cálculos iterativos y 3. Y esta es la redifinitiva

En los dos últimos artículos (aquí y aquí), los he dedicado al uso al uso de las iteraciones en Excel. También hemos visto cómo Excel, para ahorrar tiempo y ganar en velocidad, determina que cosas tiene que calcular y cuales no.

Pues nos queda hablar de un tema fundamental y es cómo y en qué orden calcula Excel. Dado que no puede calcular todas las celdas a la vez, y tiene que volver sobre sus pasos en mas de una ocasión. Excel a la hora de calcular una hoja de cálculo procede de la siguiente manera:

• Linea a línea de arriba a bajo
• En cada fila columna a columna y de izquierda a derecha.

Por supuesto, que cuando la fórmula y/o la celda en que se encuentra realizando el cálculo, si así lo exige, Excel ira a buscar las celdas precedente y vinculadas, complicándose su tarea.

Esto se explica muy bien con un modelo en el que cada fórmula sólo dependa de la fila de abajo y/o de la celda de la izquierda, se calculará mas rápido que un modelo que no respete esta fórmula.

Volviendo a nuestro último modelo (en este modelo teníamos activadas las opciones de calculo manual y el número de iteraciones era de 10):

¿Qué ocurre cuando Excel inicia el cálculo de la iteración #10?

Veamoselo repasando línea a línea el proceso. Cuando se inicia el cálculo de la iteración # 10 y encara el calculo de la línea # 6, toda la parte del modelo colocado por debajo de esa linea y a la derecha, es decir, todas las líneas 7-12, están  todavía en la condición y valores de la iteración # 9. Por lo tanto el contador B7 está en 9, y no en 10. Por ello, el porcentaje de F6 - F12  se calcula dividiendo los valores de la columna E (E6:E11) por 9.

Cuando Excel realice el cálculo de la línea #7, el contador de iteraciones B7 aumentará a 10 y el porcentaje de F6 a F11 serán correctos.

Luego pasa a realizar el calculo de la fila #8, y entonces B8 cambia a 2,  pero las filas #6 y #7 ya han sido calculadas,  ya fue comprobada el condicional "=SI(--$B$7=0;0;E8+SI($B$8=D8;1;0))", porque la prueba E6 para ver si habían salido un 2 ya se ha calculado, y por ello se ignora. Imagina que juegas a los dados y que nunca sale  un 1, un 2 o 3, ya que en estos casos, la prueba del condicional E6, E7 o E8  se calcula con la iteración anterior. ¡Los Soprano!.

El remedio es muy simple, basta con mover el bloque de celdas D5..F12, de forma que todos los cálculos se realicen de arriba a bajo y de izquierda a derecha, tomando siempre y en cada momento los valores correctos. Esta opción al modelo la puedes ver en la hoja "Arreglo" del modelo "Cálculos iterativos y 3.xlsm"

Esto es lo que se habría obtenido si los sorteos anteriores no hubieran estado amañados.

Ojo, lo importante de la Excel es que calcule bien, no que quede bonita ni el diseño, eso viene después. En los cáculos iterativos ten cuidadado donde pones la ojo, ten presente la forma de trabajar de Excel.

Tener activado el calculo automático y no el manual crea el espejismo de que Excel ha realizado algún calculo bien, pero eso no te garantiza el correcto cálculo, puede que lo consigas para alguna fila o columna, pero la totalidad de las celdas, no se logra. El arreglo total es tener los datos de origen arriba y a la izquierda. 

Si te da mucha pereza cambiar la forma de cálculo de Excel de automático a manual y viceversa, para arrancar la simulación y reinicializar el modelos, puedes insertar una macro tal y como te propongo aquí:

Puedes descargarte el modelo "Cálculos iterativos y 3" en flahs foward, totalmente gratuito basta con que estés registrado, en la web.