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í.

Propuestas pendientes en las becas del Gobierno de Navarra

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.

 

¿Cuál es el municipio que más puentes tiene en España? Cálculo del índice de “Rating Puenting 2013.

Domeño en Valencia y Granja de Rocamora en Alicante los dos pueblos de España donde mas y mejores puentes festivos hay en el 2013.

En la cultura francesa es conocido como “faire le pont”, así como entre italianos y portugueses con “el ponte”. En Alemania y Suiza el puente formado por un jueves festivo, viernes, más fin de semana se llama Brückentag, Fenstertag en Austria y brugdag en los Países Bajos. En Argentina se conocen como "feriado puente". En Israel se denomina "yom gishur"/"יום גישור". En Chile se usa la expresión "sándwich",  para referirse al día laboral entre un fin de semana y un festivo que se toma libre. En el Reino Unido un fin de semana largo o puente festivo, en el que el día de fiesta cae en viernes o lunes, se denomina bank holiday.

Puentes, acueductos y fin de semana largos los hay en todos los países, pero es en España donde lo hemos convertido en un arte.

Un gobierno central, Comunidades Autónomas, Diputaciones provinciales, y ayuntamientos, fijando calendarios laborales, siembran de festivos todo el año. Sólo hay tres días en el 2013 en los que no celebra una fiesta oficial, retribuible y no recuperable en todo España (aquí).

El Rating Puenting del 2013

El RP-2013 o “Rating Puenting” de los pueblos de España para el 2013,  mide la cantidad de puentes que un ciudadano de un municipio, puede confeccionar a lo largo del año, en otras palabra, la facilidad de utilizar días laborales para que juntando con los días festivos y/o fines de semana disfrutar unos días feriados o unos fines de semana largos.

El RP, se confecciona mediante una ponderación de varios criterios: nº de semanas con menos de 5, 4 o 3 días laborales, cercanía entre las semanas con menos de 5 días laborales, días laborales máximos entre dos festividades oficiales, cercanía de los festivos al fin de semana, etc. Este ranking se confecciona desde el municipio, aldea, pedanía, ciudad o villa, y es específico para cada población en las que se han fijado un calendario laboral oficial y se tenga sus 14 festivos, y es una base de datos de 10.272 poblaciones.  Y permite agrupar y calcular el promedio por provincia o CCAA.

Y el primer premio es para…

En el 2013, encabezan esta clasificación con 399.51 puntos dos localidades que empatan a puntos. La primera de las poblaciones es, Domeño en la provincia de Valencia y el municipio de la Granja de Rocamora en Alicante. En estas poblaciones con tan solo 10 días laborales puedes obtener hasta 35 días de fiesta consecutivos en tres tandas. En Pamplona, que además de sus Sanfermines es conocido por su puente foral,  con 6 días laborales se pueden disfrutar de 18 días festivos.

EL menor valor de RPuenting es de 217,2 y hay 20 poblaciones empatadas en esta puntuación, todas ellas de Cataluña, y entre ellas destaca Tordera, en el Maresme 

 

Domeño (Valencia)                       Granja de Rocamora (Alicante)

¿Y la CCAA donde me encuentro?

Puedes ver la situación que ocupa tu CCAA, provincia o localidad en una base de datos que hemos confeccionado en Excel 2007, y en la que encontrarás para 10.272 poblaciones, sus días festivos, la puntuación de los puentes, de cercanía a los fines de semana, la población o el RP - Rating Puenting, y que puede descargar en wannanotes. Puedes ver el por CCAA, donde observaras que unos se llevan la fama y otros cardan la lana: 

Para ve r la posición que ocupa tu ciudad puedes verlo en una Excel que descargaras en wannanotes, y colaborar con el blog . Puedes ver la clasificación de los pueblos en la ExcelPuenting 2013” descargándola en Wannanotes.

Creación de una referencia circular

Lo más habitual, es que el mensaje que nos devuelve Excel de referencia circular sea un error involuntario, un fallo a la hora de introducir las referencias o de marcar las celdas. Pero otras veces puede que sea un problema buscado. Hoy veremos una de esas situaciones.

Supongamos que queremos comprar un objeto y no disponemos de dinero en este momento para ello. Nos disponemos a pedir un préstamo para solventar esta situación de escasez de tesorería, pero no tenemos dinero ni para el principa , ni para los intereses. Si el importe del prestamos solo cubre el precio del objeto, no tendremos dinero para pagar los intereses, pero si pido dinero extra para los intereses, tendré que pagar interés sobre esos intereses, así que pido algo más para pagar los intereses delos intereses delos intereses,….. ¡Una referencia circular!

Para verlo vamos a trabajar con un sencillo y pequeño modelo, introducimos la cantidad de dinero que necesitamos para la compra  en la celda B4, B5 los intereses anuales que pagaremos, supongamos un 6% anual del importe total solicitado al banco y B7, introducimos la suma del precio y de los intereses, que será la cantidad solicitada.

Para mayor claridad, en la columna C que figura las fórmulas introducidas en la columna B.

Y ZAS! Cuando trato de validar la fórmula de la suma en B5, Excel me dice que no se puede calcular la fórmula porque hay una referencia circular:

Se trata de una garantía normal porque lo más a menudo referencias circulares son totalmente involuntarios, son los errores de diseño de del modelo.

Sin embargo en nuestro caso no es un error, queremos calcular realmente el resultado de la convergencia. Hay muchos casos de estos: calcular el precio de venta de un bien, incluyendo la comisión del vendedor, impuestos, etc.. 

Para validar las referencias circulares de cálculo, es necesario ir al comando Archivo, y en Opciones  de Excel, ficha "Fórmulas" y marcar la casilla "Habilitar cálculo iterativo" como lo hemos hecho a continuación.

Ahora, en la celda B6 aparece el resultado final, la cantidad que deberíamos solicitar al banco para que nos llegue para pagar la compra y los intereses, 106,38 eur.

Así de fácil. También podríamos haber dividido el importe de la compra por uno menos el tipo de interés y obtener el importe final, no es más que la solución al límite que sustenta este problema. Nos debería de dar el mismo importe. Lo incluyo en la celda B8, y en la celda B9 comparo si son iguales B8=B6, y me da FALSO, es decir, que el resultado es distinto, aunque no parezca pues lo he redondeado a 2 decimales, pero internamente trabaja con todos los decimales y hay pequeñas diferencias, y cada vez que pulsamos la tecla F9, la de recalcular, cambia. ¿Qué está pasando?. Es un problema del número de iteraciones, el modelo convergerá incrementando el número de iteraciones, o pulsando varias veces F9.

Puedes descargar el modelo que hemos visto, modificarlo y enredar en él, en wannannotes y colaborar con el blog, pulsando aquí.

En los siguientes post  veremos cómo solucionar este desfase y para qué sirven los dos ajustes que tiene esta ficha: Número máximo iteraciones y la desviación máxima. Inspirado por Ms. Thiriez. 
Hasta el próximo post.