# ¿Referencia circular, o no?



## RalphA (Feb 22, 2007)

Hola, amigos.  Tengo una pregunta que hacer. 
Entro, 
en C1, 1 
en D1, 1 en D2, 2 en D3, 3 y así sucesivamente hasta, digamos, D10 
en E1, =IF($C$1 = D1,$A$1+$B$1,F1), y copio hacia abajo hasta E10 
en F1, =E1, y copio hacia abajo hasta F10 

Luego, en A1 entro 20, y, en B1 entro 15. 
En E1 aparece el valor de la suma A1+B1, que es 35, en este ejemplo. 
Y, en F1, aparece el valor de E1, que es 35. 

Ahora, cambiamos el valor en C1, á 2. 
Luego, cambiamos los valores en A1 y B1, á 12 y 15. 
E1 y F1 siguen siendo 35 
E2 y F2 ahora valen 12+15, o sea, 27 

Ahora, cambiamos el valor en C1 a 3. 
Luego, cambiamos los valores en A1 y B1 á 3 y 6 
E1 y F1 siguen siendo 35 
E2 y F2 siguen siendo 27 
E3 y F3 ahora valen 3+6 = 9 

Lo que describí arriba resultó de una pregunta. En mi contestación, en C1, puse la fecha de hoy, en forma =TODAY(), que es 02/21/2007.  En D1 puse 02/22/2007, con fechas sucesivas de cada día en D2, D3, etc. 

Cuando formulé mi solución de arriba, con la fórmula que puse en la columna F, pensé que tendría uno de esos problemas de referencias circulares, y que tendría que ver cómo resolvía el problema. Para mi gran sorpresa, todo funcionó de lo más bien, y, ¡me quedé asombrado! 

Mi pregunta es: ¿por qué es que no surgió la referencia circular? O es quizá que, mientras se ponga la fórmula en la columna E antes que la fórmula de la columna F, como la fórmula en E1 ejecuta la primera parte del IF, encuentra que es cierto que C1=D1, hace la suma A1+B1, y…termina allí, sin ver al último término, resultando así que no hay referencia circular entre E1 y F1? 

No sé si mi solución al problema de entradas que cambian cada día (o cualquier período de tiempo que uno quiera) sea una solución conocida ya, pero, yo quedé impresionado de que pude resolver esa pregunta sin rendirme al VBA!


----------



## Greg Truby (Feb 22, 2007)

Rafa,

La verdad es que sí, usted está creyendo una referencia circular.  El comportamiento que usted está observando tiene algo que ver en la forma en que Excel trabaja para evitar caer en un bucle infinito.  No es una solución estable sino sumamente inestable.  Algo interesante ver, pero no recomendable para una hoja de trabajo serio.

Saludos,


----------



## galileogali (Feb 22, 2007)

Ralph: yo segui el orden meticulosamente pero sí surgió el mensaje de referencia circular, no bien copié el formula en F1 a todo el rango hasta F10. Pero cancelado los mensajes todo funciona como era de esperar.

La idea es buena. Lo que vi es que resiste "F9". 
De todas formas cambié Herramientas>>Opciones>>Calcular>> Iteración a True y Maximo de Iteraciones en 1.

Muy Bueno. Con lo que se evita una macro


----------



## RalphA (Feb 22, 2007)

Greg:

¿Inestable?  Esto me hace hacer las preguntas siguientes:
1. ¿Quiere decir esto, quizá, que no siempre dará el resultado correcto? 
2. ¿Cómo hace uno para confirmar que este método es inestable?  
3. ¿Acaso existe un método general para probar la inestabilidad de qualquier fórmula?
4. ¿O, quizá existe una prueba publicada, en la cual se demuestra en qué consiten las inestabilidades, y cómo evitar esa condición?


----------



## RalphA (Feb 22, 2007)

galileogali:

Gracias por su paciente experimentación.  Revisé mi Tools, Options, Calculations, y veo que tengo 100 repeticiones con una toleracina de 0.001,  Aún así, yo no obtuve ningún mensaje de error.  Eso sí, uno tiene que poner la entrada en C1, o dejar que la fecha aparezca en C1, antes de cambiar los valores en las columnas A y B, para que funcione.

Pero, dice Greg que este método es inestable, por lo que no se debe usar, bajo pena de errores, quizá inesperados.


----------



## galileogali (Feb 22, 2007)

Greg:
¿No logra detener el bucle, el setear las Iteraciones a un máximo de 1?


----------



## Greg Truby (Feb 22, 2007)

Rafa,

Sin duda hay maneras de evaluar la estabilidad.  Pero no los conozco.  Seguro que alguien como Juan Pablo o Tushar Mehta o Charles Williams nos podría decir.  Pero sin duda no es estable porque depende, como digo, en la manera que está usando Excel para evitar los bucles infinitos que referencias circulares creen.  En el contexto limitado aquí, creo que sí da resultados constantes porque usted, Gali y yo hemos recibido resultados identicos - usted en XL97, yo en XL2003, no sé qué versión corre Gali.  

Pero, pruebe eso: en F11 ponga =SUM(F1:F10) o alguna otra fórmula que depende de los valores en E1 a F10.  Para mí, me está dando cero.  El ciclo de cálculo está parando no sabemos donde.  Por eso no podemos estar seguros que celdas que dependen de E1 a F10 van a calcular correctamente.


----------



## galileogali (Feb 22, 2007)

Ralph:
Cambie tu fórmula a algo más Obviamente circular: en E1 =IF($C$1=D1,$A$1+$B$1,E1) y borrando la columna F1, como que me pareció innecesaria....
y sigue funcionando.....
De todas maneras, seguire usando VBA, PARA ESTAS ASIGNACIONES DE VALOR

GALI


----------



## galileogali (Feb 22, 2007)

Probe SUMA(E1:E10), RESULTADO CORRECTO.
cambié valores>>>cambió la suma.....

ITERACIONES EN 1, Excel XP

GALI


----------



## Greg Truby (Feb 22, 2007)

Sí, Gali, tenés razón.  Poner repiticiones a 1 (o algo como 100 para estar más seguro todavía) y sí da resultados.  Parece que uno podría aprovechar del descubrimiento de Rafa debajo de circunstancias limitadas.  Pero no lo haría yo tampoco porque yo no tendría confianza en la habilidad de usuarios de mi hoja en hacer referencias a mis números.


----------



## RalphA (Feb 22, 2007)

Hola, amigos.  Tengo una pregunta que hacer. 
Entro, 
en C1, 1 
en D1, 1 en D2, 2 en D3, 3 y así sucesivamente hasta, digamos, D10 
en E1, =IF($C$1 = D1,$A$1+$B$1,F1), y copio hacia abajo hasta E10 
en F1, =E1, y copio hacia abajo hasta F10 

Luego, en A1 entro 20, y, en B1 entro 15. 
En E1 aparece el valor de la suma A1+B1, que es 35, en este ejemplo. 
Y, en F1, aparece el valor de E1, que es 35. 

Ahora, cambiamos el valor en C1, á 2. 
Luego, cambiamos los valores en A1 y B1, á 12 y 15. 
E1 y F1 siguen siendo 35 
E2 y F2 ahora valen 12+15, o sea, 27 

Ahora, cambiamos el valor en C1 a 3. 
Luego, cambiamos los valores en A1 y B1 á 3 y 6 
E1 y F1 siguen siendo 35 
E2 y F2 siguen siendo 27 
E3 y F3 ahora valen 3+6 = 9 

Lo que describí arriba resultó de una pregunta. En mi contestación, en C1, puse la fecha de hoy, en forma =TODAY(), que es 02/21/2007.  En D1 puse 02/22/2007, con fechas sucesivas de cada día en D2, D3, etc. 

Cuando formulé mi solución de arriba, con la fórmula que puse en la columna F, pensé que tendría uno de esos problemas de referencias circulares, y que tendría que ver cómo resolvía el problema. Para mi gran sorpresa, todo funcionó de lo más bien, y, ¡me quedé asombrado! 

Mi pregunta es: ¿por qué es que no surgió la referencia circular? O es quizá que, mientras se ponga la fórmula en la columna E antes que la fórmula de la columna F, como la fórmula en E1 ejecuta la primera parte del IF, encuentra que es cierto que C1=D1, hace la suma A1+B1, y…termina allí, sin ver al último término, resultando así que no hay referencia circular entre E1 y F1? 

No sé si mi solución al problema de entradas que cambian cada día (o cualquier período de tiempo que uno quiera) sea una solución conocida ya, pero, yo quedé impresionado de que pude resolver esa pregunta sin rendirme al VBA!


----------



## galileogali (Feb 22, 2007)

Además:
Poner Iteraciones en 1, vale para todo el Libro o para toda la hoja?

Si vale para todo el libro, creo que es arriesgado el efecto que podria producir sobre la Smart Calculation de la que tanto habla C.W., pero si vale solo para determinada hoja ( no creo que se asi), podria uno limitarse al Planteo de Ralph, en esa hoja y manejarse con Iteracion Standard en el resto del libro

latente: ¿alcance de Iteracion: Libro u Hoja?

GALI


----------



## RalphA (Feb 22, 2007)

Greg:

Probé tu fórmula de =SUM(F1:F10), poniéndola en la celda F11.  Resultado correcto! 

En todo caso, escucho tus palabras de muchísima más experiencia que yo. …Pero, como dijo Galileo, “E per si muove”, o algo así.  Y, no me convencen las palabras, solamente una – o varias - pruebas que yo pueda repetir.  

Como la fórmula que tu usastes te dió un error, al darte un 0 por resultado, eso parece decirme a mi que mi viejo Excel97 es más “estable” que tu Excel 2003.  (Ja, ja, me dá risa, pero sin intención de ofender, por supuesto.  Es que dice el dicho, “Más vale viejo conocido, que nuevo por conocer” – bueno, no exactamente, pero me gusta más así, para este caso.)   
===============================================

Galileogali:

Probé eliminar la columna F.  Como á vos, á mi me sigue dando el resultado correcto en la columna E.  Más preguntas, ¿verdad?


----------



## Greg Truby (Feb 23, 2007)

> latente: ¿alcance de Iteracion: Libro u Hoja?
> ~GALI



Gali,

La opción de como calcular es una propiadad interesante.  Excel usa el ajuste (setting) del cuaderno que se abrió inicialmente.  Entonces si usted tiene un cuaderno PERSONAL, este cuaderno va a controlar esa opción cada vez que Excel arranca.  Pero, si usted la cambia manualmente a otro, entonces cada cuaderno en Excel se cambia.  Los ajustes de cálculo se quedan en efecto para cada cuaderno nuevo o existente que se abre depués de haber hecho el cambio.  Entonces si tiene un cuaderno PERSONAL y esto tiene la opción no marcado, aunque usted archiva el cuaderno de Rafa con esta opción marcada, al abrir Excel el próximo día habrá que cambiarlo otra vez -- que implica usar un macro.  Y evitar macros fue el propósito de todo esto, ¿no?


----------



## galileogali (Feb 23, 2007)

Resumiendo:
cualquier archivo abierto con posterioridad a una Worbook_Open, o Auto_open, dejarà la Calculation en cualquier valor, y hacer una macro como bien dice Greg, para evitar otra no es un buen negocio. Sin embargo, como experimento, resulta interesante, porque obliga a repensar la Calculation en Excel, de un modo más ajustado y preciso.


----------



## RalphA (Feb 23, 2007)

Hice este experimento:  
Usando la fórmula que publiqué inicialmente, hice click en Tools, Options, Calculation, y observé que estaba marcada la cajita “Iteration”.  Al quitar la marca en al cajita “Iteration”, y volver a la hoja, obtuve el error de una referencia circular.  

Lo de arriba me hace pensar que “inestable” debe referirse, al menos en el caso presente, a que puede dar ese error si una persona tiene la cajita “Iteration” en blanco, mientras que le funcionará bien a una persona que sí tiene esa cajita marcada.  Es decir, que puede dar un resultado “inestable”, de acuerdo a la condicón de esa cajita.  

Entiendo por qué Greg no quisiera usar, para un trabajo “serio”, el método que yo usé.  Pero, estoy seguro que habrán muchos casos en que se requiere que esa cajita esté marcada, por el hecho que se requieren las reiteraciones para uno o más cálculos.  En ese caso, para un trabajo de ingeniería, que yo calificaría definitivamente de serio, ¿sería indispensable poner un aviso, para indicar que se debe marcar esa cajita?  ¿O es que se debe usar código para hacer este tipo de cálculos en trabajos serios?

Como ejemplo de lo que, talvez, se pueda llamar “inestable”, un programador en el lenguaje usado en QuickBASIC (QB) dijo que ya no usa QB, porque encuentra que, como un ejemplo, de 50 programs que había hecho en el pasado, usando DOS, ahora solamente dos le trabajaron bajo Windows XP a varias personas.  Yo, también, tuve muchos problemas con QB y XP (creía yo), hasta que encontré, después de muchas consultas y pruebas, que la culpa no era de XP, sino de un program de un “scanner” viejo que yo tenía, program que corría automáticamente al empezar la computadora, y que seguía corriendo en el fondo.  Una vez que deshabilité el correr automáticamente a ese programa, todo resultó a la maravilla.  Así, si algún programa o archivo encuentra  problemas en correr en una computadora, al notarse que usa XP y que viene de una que no usaba XP, en la cual siempre corría correctamente, se dirá que no es compatible con XP, ya que esa respuesta le echa la culpa a un tercero y “absuelve” de culpa al programa.  Así se quita del problema de investigar por qué no corre bien en XP.  

Claro, yo veo que no es razonable esperar que el autor de un trabajo que se difunde ampliamente tenga que lidiar con las complicaciones evitables.  Así, no usar fórmulas que dependen de cómo está una computadora configurada, evitará, a su vez, el tener clientes que experimentan problemas evitables.  Y, esto me hace entender el evitar usar fórmulas “inestables”.


----------



## Greg Truby (Feb 26, 2007)

Bueno - tal vez "inestable" no sea la palabra más adecuada posible.  Tal vez "no robusto" o "no fuerte" sean términos mejores.  "Inestable" implica que los resultados son impredicibles - y realmente no es asi.  Lo más probable es que sí, uno podría predecir qué van a ser los resultados.  Ni sé cuantas veces he ofrecido una fórmula a alguien y aparece Aladin o Fairwinds con una fórmula un poco (o a veces muy) diferente y siempre Aladin tiene una notita "the following is a bit more robust" que quiere decir que es más dificil provocar una falla en la solución.  Esto de usar referencia circulares, para mí, sería una solución débil en el sentido que me parece fácil "quebrarla".

Pero siempre interesante estudiar.  Definitivamente aprendí alguito nuevo y le agradezco, Rafa, por el diálogo.


----------



## RalphA (Feb 26, 2007)

Entendido, Greg.  Y, ...gracias.


----------

