Si has usado Microsoft Excel durante algún tiempo, lo más probable es que hayas creado inadvertidamente una fórmula que resulte en una referencia circular. Esto significa que la fórmula que has creado incluye la celda donde reside la fórmula. En casi todos los casos en Excel cuando creamos fórmulas nos referimos a otras celdas que no sean aquella donde reside nuestra fórmula. Sin embargo, hay situaciones en las que puede querer crear a propósito una referencia circular.
Lo más probable es que haya creado una referencia circular accidental al crear un SUM que incluya el total de la fila en sí, como se muestra en la Figura 1. Los resultados que veas variarán según tu versión de Excel:
- Excel 2013: Aparecerá un simple aviso que le informará de que ha creado una referencia circular, dándole la opción de elegir entre Aceptar o Cancelar. Si hace clic en Aceptar, la fórmula aparece en la celda pero podría mostrar un cero, como se muestra en la Figura 1.
- Excel 2010 y anteriores… Puede que tengas la impresión de que tu ordenador está a punto de colapsar. Excel no sólo te da un aviso, sino que también dibuja una o más flechas en la pantalla y muestra una pantalla de ayuda.
Figura 1: La fórmula muestra cero porque ha creado una referencia circular.
En cada versión de Excel aparecerán las palabras Referencia Circular en la barra de estado, junto con una celda de referencia. Todas estas indicaciones desaparecen una vez que se corrige la fórmula.
Sin embargo, puede haber situaciones en las que se desee crear intencionadamente una referencia circular. Mi ejemplo de enseñar referencias circulares se remonta a mis días cuando trabajaba en bienes raíces comerciales. Digamos que un promotor inmobiliario ha conseguido la financiación para construir un edificio de 100 millones de dólares. El promotor ganará una comisión por sus esfuerzos; en este caso digamos el 3 por ciento. Sin embargo, los contratos se escriben de manera que no es el 3 por ciento del costo del edificio, sino el 3 por ciento del costo total del proyecto. El costo total del proyecto es de 100 millones de dólares más los honorarios del promotor. Ahí está la referencia circular, ya que los honorarios son parte del cálculo. La figura 2 muestra las fórmulas implicadas.
Figura 2: La celda H3 contiene la fórmula SUM(H1:H2), por lo que la cuota de desarrollo inicialmente no se calcula.
Fíjese que inicialmente la tarifa del promotor no se calcula. En esta situación tenemos que instruir a Excel para que calcule los honorarios del desarrollador por nosotros estableciendo una opción de Excel, como se muestra en la Figura 3:
- Elija Archivo, Opciones y luego Fórmulas.
- Haga clic en la casilla de verificación de Habilitar cálculos iterativos y luego haga clic en Aceptar.
Figura 3: Habilitar los cálculos iterativos para calcular la tasa de desarrollo.
Tenga en cuenta que este es un ajuste específico del libro de trabajo, por lo que tendrá que habilitar este ajuste para cada libro de trabajo individual que implique referencias circulares. Una vez que cierres el cuadro de diálogo Opciones, verás que Excel calcula la tarifa de desarrollo de 3.092.784 dólares. Escribir el contrato de manera que los honorarios del promotor se basen en el coste total le da al promotor un extra de 92.784 dólares, ya que el 3 por ciento del coste del edificio en sí habría sido de 3.000.000 de dólares.
Si volvemos a la figura 3, notará que hay dos ajustes adicionales relacionados con los cálculos iterativos:
- Iteraciones máximas: El valor por defecto es 100, lo que significa que Excel intentará 100 veces resolver el cálculo antes de rendirse. Este ajuste le protege de poner a Excel en un estado de cálculo perpetuo.
- Cambio máximo: Excel intenta hasta 100 veces resolver el cálculo y se detiene cuando la diferencia entre las iteraciones no es más de 0,001.
Sólo puedo pensar en un par de veces en mi carrera que he necesitado ajustar cualquiera de estas opciones. En mi experiencia, cambiar el cambio máximo a .01 debería ser suficiente para la mayoría de los casos. Asegúrate de guardar tu libro de trabajo antes de jugar con las Iteraciones Máximas o el Cambio Máximo en caso de que accidentalmente pongas a Excel en un estado del que no puedas salir sin estrellar el programa.
En un próximo artículo describiré una característica oculta en Excel que hace fácil encontrar todas las referencias circulares dentro de un libro de trabajo. Tengan en cuenta que las referencias circulares deben ser usadas con cuidado en Excel. Si simplemente habilitas los cálculos iterativos para que las indicaciones y las flechas desaparezcan, puedes terminar con resultados engañosos. Por ejemplo, esa función de suma simple que he creado devuelve 60.000 en lugar de 600 si se activa la opción de habilitar los cálculos iterativos, como se muestra en la figura 4.
Figura 4: Tenga cuidado de habilitar los cálculos iterativos porque pueden llevar a resultados erróneos.
