¿Referencia circular, o no?

[RalphA]

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]

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]

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]

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]

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]

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

 

[Greg Truby]

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]

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]

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

ITERACIONES EN 1, Excel XP

GALI

 

[Greg Truby]

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.