Normalmente vas a querer eliminar referencias circulares en Excel porque suelen ser un error, pero puede que sean intencionadas. Sin embargo, Excel, por defecto, no las permite. Aquí te explico cómo habilitarlas si las quieres usar y cómo eliminarlas si no.
Por si no tienes al 100% claro qué es una referencia circular, recuerda que es una referencia en una fórmula o función a si mismo que provoca un bucle en el cálculo.
¿Qué encontrarás aquí?
Por ejemplo: si la celda A1 contiene la fórmula =A1+A2, habremos provocado una referencia circular. El resultado de la fórmula forma parte del cálculo, la pescadilla que se muerde la cola.
Esto, en principio, es absurdo y, por tanto, Excel no lo permite.
Cómo permitir referencias circulares en Excel
Sin embargo, hay lógicas complejas donde un bucle de este tipo puede tener sentido. Por eso, Excel dispone de la opción de cálculo iterativo en las opciones de Excel a las que puedes acceder vía “Archivo / Opciones” y seleccionando la sección de Fórmulas en el cuadro de diálogo que se mostrará y que puedes ver a continuación:
Por defecto, esta opción está deshabilitada y Excel avisará del error cuando entres en un bucle de este tipo.
Activando la opción, Excel ejecutará el bucle. Pero hay un problema: siendo un bucle este proceso, en principio, no terminaría nunca y Excel se quería calculando hasta el fin de los tiempos. Por ese motivo, obliga a especificar un número máximo de iteraciones.
Ejemplo de cálculo iterativo legítimo con una referencia circular
Hay ciertos problemas lógicos que se pueden modelar/resolver de esta manera como los que nos encontramos en el álgebra, por ejemplo .
Pongamos un ejemplo sencillo: tenemos una ciudad grande (con muchos habitantes) y otra pequeña. Sabemos que la grande tiene tres veces más habitantes que la pequeña y la diferencia con ésta es de 265.000 habitantes.
¿Cuántos habitantes tiene la ciudad pequeña?
Esto lo pueden modelar de la siguiente manera:
- La celda “Ciudad Pequeña”: =”Ciudad Grande/3”
- La celda “Ciudad Grande”: =”Ciudad Pequeña + 265.000”
Para esto usaremos las celdas C2 y C3 con las respectivas fórmulas:
- =C3/3
- =C2+265000
Aquí lo puedes ver:
Como ves, Excel ha hallado el resultado correcto que es 132.500 habitantes en la ciudad pequeña.
¿Qué ha pasado aquí? Pues que va haciendo sucesivas iteraciones obteniendo resultados intermedios:
- La celda de la ciudad pequeña la considera inicialmente con valor 0 y la grande con 000.
- En la segunda iteración ya tiene un valor para la grande que es dividido en 3 es 333,33..., que sumado al valor de la grande es 353.333,33...
- En la tercera iteración divide333,33… entre 3 y el resultado lo vuelve a sumar a 265.000.
- Y así sucesivamente…
Con las iteraciones suficientes llegará al resultado final. Es muy bonito limitar sucesivamente las iteraciones a muy pocas (a 1, 2, 3, …) y observar cómo van cambiando los resultados de iteración en iteración.
Si limitamos las iteraciones a 10, por ejemplo, las cifras son de 132499,9169 y 397499,9169 respectivamente. Podemos ver cómo el cálculo ya se ha aproximado muchísimo al resultado correcto, pero aún sin alcanzarlo al 100%.
Cómo eliminar referencias circulares
Para eliminar referencias circulares, comprueba en primer lugar que la opción de cálculo iterativo no esté habilitada para que Excel te avise de los errores y los puedas identificar más fácilmente.
Normalmente, la solución del problema será trivial porque la referencia circular será un error, es decir, que has cometido un error en la fórmula. En todo caso, habrá que sacar simplemente la celda que causa el problema del bucle, es decir, llevarla a otra celda.
Si la depuración de las referencias circulares se te atascar, puedes usar también la funcionalidad de rastreo de dependencias entre celdas de Excel que se describe aquí:
Cómo encontrar una referencia circular en Excel
Querer encontrar referencias circulares en Excel responde normalmente a un error de referencia circular. Aquí te enseño cómo encontrarlas.
Revisa bien tus fórmulas y sigue este proceso hasta que Excel deje de avisar de problemas de referencias circulares.
