Las fórmulas de Excel no funcionan: ¡posibles razones y cómo solucionarlo!

Si trabaja con fórmulas en Excel, tarde o temprano se encontrará con el problema de que las fórmulas de Excel no funcionan en absoluto (o dan un resultado incorrecto).

Si bien hubiera sido genial si hubiera habido solo unas pocas razones posibles para el mal funcionamiento de las fórmulas. Desafortunadamente, hay demasiadas cosas que pueden salir mal (y a menudo lo hacen).

Pero dado que vivimos en un mundo que sigue el principio de Pareto, si busca algunos problemas comunes, es probable que resuelva el 80% (o tal vez incluso el 90% o el 95% de los problemas en los que las fórmulas no funcionan en Excel).

Y en este artículo, destacaré los problemas comunes que probablemente sean la causa de su Las fórmulas de Excel no funcionan.

¡Entonces empecemos!

Sintaxis incorrecta de la función

Permítanme comenzar señalando lo obvio.

Cada función en Excel tiene una sintaxis específica, como la cantidad de argumentos que puede tomar o el tipo de argumentos que puede aceptar.

Y en muchos casos, la razón por la que sus fórmulas de Excel no funcionan o dan un resultado incorrecto podría ser un argumento incorrecto (o argumentos faltantes).

Por ejemplo, la función BUSCARV toma tres argumentos obligatorios y un argumento opcional.

Si proporciona un argumento incorrecto o no especifica el argumento opcional (donde es necesario para que la fórmula funcione), le dará un resultado incorrecto.

Por ejemplo, suponga que tiene un conjunto de datos como se muestra a continuación donde necesita conocer la puntuación de Mark en el Examen 2 (en la celda F2).

Si utilizo la fórmula siguiente, obtendré el resultado incorrecto, porque estoy usando el valor incorrecto en el tercer argumento (uno que solicita el número de índice de columna).

= BUSCARV (A2, A2: C6,2, FALSO)

En este caso, la fórmula calcula (ya que devuelve un valor), pero el resultado es incorrecto (en lugar de la puntuación en el Examen 2, da la puntuación en el Examen 1).

Otro ejemplo en el que debe tener cuidado con los argumentos es cuando usa BUSCARV con la coincidencia aproximada.

Dado que necesita usar un argumento opcional para especificar dónde desea que BUSCARV haga una coincidencia exacta o aproximada, no especificar esto (o usar el argumento incorrecto) puede causar problemas.

A continuación se muestra un ejemplo en el que tengo los datos de calificaciones para algunos estudiantes y quiero extraer las calificaciones en el Examen 1 para los estudiantes en la tabla de la derecha.

Cuando utilizo la siguiente fórmula VLOOKUP, me da un error para algunos nombres.

= BUSCARV (E2, $ A $ 2: $ C $ 6,2)

Esto sucede porque no he especificado el último argumento (que se usa para determinar si hacer una coincidencia exacta o aproximada). Cuando no especifica el último argumento, automáticamente realiza una coincidencia aproximada de forma predeterminada.

Como necesitábamos hacer una coincidencia exacta en este caso, la fórmula devuelve un error para algunos nombres.

Si bien he tomado el ejemplo de la función BUSCARV, en este caso, esto es algo que puede aplicarse a muchas fórmulas de Excel que también tienen argumentos opcionales.

Lea también: Identifique errores mediante la depuración de fórmulas de Excel

Espacios adicionales que provocan resultados inesperados

Los espacios iniciales y finales son difíciles de encontrar y pueden causar problemas cuando usa una celda que los tiene en fórmulas.

Por ejemplo, en el siguiente ejemplo, si intento usar BUSCARV para obtener la puntuación de Mark, me da el error # N / A (el error no disponible).

Si bien puedo ver que la fórmula es correcta y el nombre "Marca" está claramente en la lista, lo que no puedo ver es que hay un espacio al final en la celda que tiene el nombre (en la celda D2).

Excel no considera que el contenido de estas dos celdas sea el mismo y, por lo tanto, lo considera una falta de coincidencia al obtener el valor mediante BUSCARV (o podría ser cualquier otra fórmula de búsqueda).

Para solucionar este problema, debe eliminar estos caracteres de espacio adicionales.

Puede hacerlo mediante cualquiera de los siguientes métodos:

1. Limpie la celda y elimine los espacios iniciales / finales antes de usarla en fórmulas
2. Utilice la función TRIM dentro de la fórmula para asegurarse de que se ignoren los espacios iniciales / finales / dobles.

En el ejemplo anterior, puede utilizar la fórmula siguiente para asegurarse de que funcione.

= BUSCARV (RECORTAR (D2), $ A $ 2: $ B $ 6,2,0)

Si bien he tomado el ejemplo de BUSCARV, este también es un problema común cuando se trabaja con funciones de TEXTO.

Por ejemplo, si uso la función LEN para contar el número total de caracteres en una celda, si hay espacios al principio o al final, estos también se contarían y darían un resultado incorrecto.

Quitar / Cómo arreglar: Si es posible, limpie sus datos eliminando los espacios iniciales / finales o dobles antes de usarlos en fórmulas. Si no puede cambiar los datos originales, utilice la función TRIM en fórmulas para solucionarlo.

Usar cálculo manual en lugar de automático

Esta configuración puede volverlo loco (si no sabe qué es lo que está causando todos los problemas).

Excel tiene dos modos de cálculo: Automático y Manual.

Por defecto, el modo automático está habilitado, lo que significa que en caso de que use una fórmula o realice algún cambio en las fórmulas existentes, automáticamente (e instantáneamente) hace el cálculo y me da el resultado.

Este es el escenario al que todos estamos acostumbrados.

En la configuración automática, cada vez que realiza algún cambio en la hoja de trabajo (como ingresar una nueva fórmula incluso en un texto en una celda), Excel recalcula automáticamente todo (sí, todo).

Pero en algunos casos, las personas habilitan la configuración de cálculo manual.

Esto se hace principalmente cuando tiene un archivo de Excel pesado con muchos datos y fórmulas. En tales casos, es posible que no desee que Excel vuelva a calcular todo cuando realice pequeños cambios (ya que puede tomar unos segundos o incluso minutos) para que se complete este nuevo cálculo.

Si habilita el cálculo manual, Excel no calculará a menos que lo fuerce.

Y esto puede hacerle pensar que su fórmula no se está calculando.

Todo lo que necesita hacer en este caso es volver a configurar el cálculo en automático o forzar un nuevo cálculo presionando la tecla F9.

A continuación se muestran los pasos para cambiar el cálculo de manual a automático:

1. Haga clic en la pestaña Fórmula
2. Haga clic en Opciones de cálculo
3. Seleccione Automático

Importante: en caso de que cambie el cálculo de manual a automático, es una buena idea crear una copia de seguridad de su libro de trabajo (en caso de que esto haga que su libro de trabajo se cuelgue o que Excel se bloquee)

Quitar / Cómo arreglar: Si nota que sus fórmulas no dan el resultado esperado, intente algo simple en cualquier celda (como agregar 1 a una fórmula existente. Una vez que identifique el problema como aquel en el que el modo de cálculo debe cambiarse, haga un cálculo de fuerza utilizando F9.

¡Eliminando filas / columnas / celdas que conducen a #REF! Error

Una de las cosas que puede tener un efecto devastador en sus fórmulas existentes en Excel es cuando elimina cualquier fila / columna que se haya utilizado en los cálculos.

Cuando esto sucede, a veces, Excel ajusta la referencia en sí y se asegura de que las fórmulas funcionen bien.

Y aveces… no puede.

Afortunadamente, una indicación clara que se obtiene cuando las fórmulas se rompen al eliminar celdas / filas / columnas es el #REF. error en las celdas. Este es un error de referencia que le indica que hay algún problema con las referencias en la fórmula.

Permítanme mostrarles lo que quiero decir con un ejemplo.

A continuación, he usado la fórmula SUM para agregar las celdas A2: A6.

Ahora, si elimino cualquiera de estas celdas / filas, ¡la fórmula SUM devolverá un #REF! error. Esto sucede porque cuando eliminé la fila, la fórmula no sabe a qué hacer referencia ahora.

¡Puede ver que el tercer argumento de la fórmula se ha convertido en #REF! (que anteriormente se refería a la celda que eliminamos).

Quitar / Cómo arreglar: Antes de eliminar cualquier dato que se esté utilizando en fórmulas, asegúrese de que no haya errores después de la eliminación. También se recomienda que cree una copia de seguridad de su trabajo con regularidad para asegurarse de tener siempre algo a lo que recurrir.

Colocación incorrecta de paréntesis (BODMAS)

A medida que sus fórmulas comienzan a hacerse más grandes y complejas, es una buena idea usar paréntesis para tener absolutamente claro qué partes deben estar juntas.

En algunos casos, es posible que tenga el paréntesis en el lugar incorrecto, lo que puede dar un resultado incorrecto o un error.

Y en algunos casos, se recomienda utilizar paréntesis para asegurarse de que la fórmula comprenda lo que debe agruparse y calcularse primero.

Por ejemplo, suponga que tiene la siguiente fórmula:

=5+10*50

En la fórmula anterior, el resultado es 505 ya que Excel primero hace la multiplicación y luego la suma (ya que hay un orden de precedencia cuando se trata de operadores).

Si desea que primero haga la suma y luego la multiplicación, debe usar la siguiente fórmula:

=(5+10)*50

En algunos casos, el orden de precedencia puede funcionar para usted, pero aún se recomienda que use el paréntesis para evitar confusiones.

Además, en caso de que esté interesado, a continuación se muestra el orden de precedencia de varios operadores que se utilizan a menudo en las fórmulas:

Operador	Descripción	Orden de precedencia
: (colon)	Abarcar	1
(único espacio)	Intersección	2
, (coma)	Unión	3
-	Negación (como en -1)	4
%	Porcentaje	5
^	Exponenciación	6
* y /	Multiplicación y división	7
+ y -	Suma resta	8
&	Concatenación	9
= =	Comparación	10

Quitar / Cómo arreglar: Utilice siempre paréntesis para evitar confusiones, incluso si conoce el orden de precedencia y lo está utilizando correctamente. Tener paréntesis facilita la auditoría de fórmulas.

Uso incorrecto de referencias de celda absolutas / relativas

Cuando copia y pega fórmulas en Excel, ajusta automáticamente las referencias. A veces, esto es exactamente lo que desea (principalmente cuando está copiando y pegando fórmulas en la columna) y, a veces, no desea que esto suceda.

Una referencia absoluta es cuando corrige una referencia de celda (o referencia de rango) para que no cambie cuando copia y pega fórmulas, y una referencia relativa es aquella que cambia.

Puede leer más sobre referencias absolutas, relativas y mixtas aquí.

Puede obtener un resultado incorrecto en caso de que olvide cambiar la referencia a una absoluta (o viceversa). Esto es algo que me sucede con bastante frecuencia cuando utilizo fórmulas de búsqueda.

Dejame mostrarte un ejemplo.

A continuación, tengo un conjunto de datos en el que quiero obtener el puntaje en el Examen 1 para los nombres en la columna E (un caso de uso simple de VLOOKUP)

A continuación se muestra la fórmula que utilizo en la celda F2 y luego la copio en todas las celdas debajo de ella:

= BUSCARV (E2, A2: B6,2,0)

Como puede ver, esta fórmula da un error en algunos casos.

Esto sucede porque no he bloqueado el argumento de la matriz de la tabla; es A2: B6 en la celda F2, mientras que debería haber sido $ A $ 2: $ B $ 6

Al tener estos signos de dólar antes del número de fila y el alfabeto de columna en una referencia de celda, estoy obligando a Excel a mantener estas referencias de celda fijas. Entonces, incluso cuando copie esta fórmula, la matriz de la tabla continuará refiriéndose a A2: B6

Consejo profesional: Para convertir una referencia relativa en una absoluta, seleccione esa referencia de celda dentro de la celda y presione la tecla F4. Notaría que cambia agregando los signos de dólar. Puede continuar presionando F4 hasta que obtenga la referencia que desea.

Referencia incorrecta a nombres de hojas / libros de trabajo

Cuando se refiere a otras hojas o libros de trabajo en una fórmula, debe seguir un formato específico. Y en caso de que el formato sea incorrecto, obtendrá un error.

Por ejemplo, si quiero hacer referencia a la celda A1 en Sheet2, la referencia sería = Hoja2! A1 (donde hay un signo de exclamación después del nombre de la hoja)

Y en caso de que haya varias palabras en el nombre de la hoja (digamos que son Datos de ejemplo), la referencia sería = "Datos de ejemplo"! A1 (donde el nombre está entre comillas simples seguido de un signo de exclamación).

En caso de que se refiera a un libro de trabajo externo (digamos que se refiere a la celda A1 en "Hoja de ejemplo" en el libro de trabajo llamado "Libro de trabajo de ejemplo"), la referencia será como se muestra a continuación:

= '[Example Workbook.xlsx] Ejemplo de hoja'! $ A $ 1

Y en caso de que cierre el libro de trabajo, la referencia cambiaría para incluir la ruta completa del libro de trabajo (como se muestra a continuación):

= 'C: \ Users \ sumit \ Desktop \ [Example Workbook.xlsx] Ejemplo de hoja'! $ A $ 1

En caso de que termine cambiando el nombre del libro de trabajo o la hoja de trabajo a la que se refiere la fórmula, ¡le dará un #REF! error.

Lea también: Cómo encontrar enlaces externos y referencias en Excel

Referencias circulares

Una referencia circular es cuando se refiere (directa o indirectamente) a la misma celda donde se calcula la fórmula.

A continuación se muestra un ejemplo simple, donde uso la fórmula SUM en la celda A4 mientras la uso en el cálculo en sí.

= SUMA (A1: A4)

Aunque Excel le muestra un mensaje que le informa sobre la referencia circular, no lo hará para todas las instancias. Y esto puede darle un resultado incorrecto (sin ninguna advertencia).

En caso de que sospeche que hay una referencia circular en juego, puede verificar qué celdas la tienen.

Para ello, haga clic en la pestaña Fórmula y, en el grupo "Auditoría de fórmulas", haga clic en el icono desplegable Comprobación de errores (la pequeña flecha que apunta hacia abajo).

Pase el cursor sobre la opción Referencia circular y le mostrará la celda que tiene el problema de referencia circular.

Celdas formateadas como texto

Si se encuentra en una situación en la que tan pronto como escribe la fórmula y presiona enter, ve la fórmula en lugar del valor, es un caso claro de que la celda se formatea como texto.

Cuando una celda se formatea como texto, considera la fórmula como una cadena de texto y la muestra como está.

No lo obliga a calcular y dar el resultado.

Y tiene una solución fácil.

1. Cambie el formato a "General" de "Texto" (está en la pestaña Inicio en el grupo Números)
2. Vaya a la celda que tiene la fórmula, entre en el modo de edición (use F2 o haga doble clic en la celda) y presione Enter

En caso de que los pasos anteriores no resuelvan el problema, otra cosa que debe verificar es si la celda tiene un apóstrofe al principio. Mucha gente agrega un apóstrofo para convertir fórmulas y números en texto.

Si hay un apóstrofe, simplemente puede eliminarlo.

El texto se convierte automáticamente en fechas

Excel tiene el mal hábito de convertir lo que parece una fecha en una fecha real. Por ejemplo, si ingresa 1/1, Excel lo convertiría al 1 de enero del año actual.

En algunos casos, esto puede ser exactamente lo que desea y, en algunos casos, esto puede funcionar en su contra.

Y dado que Excel almacena los valores de fecha y hora como números, tan pronto como ingresa 1/1, lo convierte en un número que representa el 1 de enero del año actual. En mi caso, cuando hago esto, lo convierte en el número 43831 (para 01-01-2020).

Esto podría alterar sus fórmulas si usa estas celdas como argumento en una fórmula.

¿Cómo arreglar esto?

Nuevamente, no queremos que Excel elija automáticamente el formato por nosotros, por lo que debemos especificar claramente el formato nosotros mismos.

A continuación, se muestran los pasos para cambiar el formato a texto para que no convierta automáticamente el texto en fechas:

1. Seleccione las celdas / rango donde desea cambiar el formato
2. Haga clic en la pestaña Inicio
3. En el grupo Número, haga clic en el menú desplegable Formato
4. Haga clic en Texto

Ahora, cada vez que ingrese algo en las celdas seleccionadas, se considerará como texto y no se cambiará automáticamente.

Nota: Los pasos anteriores solo funcionarían para los datos ingresados ​​después de que se haya cambiado el formato. No cambiará ningún texto que se haya convertido a la fecha antes de realizar este cambio de formato.

Otro ejemplo en el que esto puede ser realmente frustrante es cuando ingresa un texto / número que tiene ceros a la izquierda. Excel elimina automáticamente estos ceros iniciales ya que los considera inútiles. Por ejemplo, si ingresa 0001 en una celda, Excel lo cambiará a 1. En caso de que desee mantener estos ceros a la izquierda, siga los pasos anteriores.

Las filas / columnas ocultas pueden dar resultados inesperados

Este no es un caso de fórmula que le da un resultado incorrecto, sino de usar la fórmula incorrecta.

Por ejemplo, suponga que tiene un conjunto de datos como se muestra a continuación y quiero obtener la suma de todas las celdas visibles en la columna C.

En la celda C12, he usado la función SUM para obtener el valor de venta total para todos estos registros dados.

¡Hasta ahora tan bueno!

Ahora, aplico un filtro a la columna del artículo para mostrar solo los registros de ventas de impresoras.

Y aquí está el problema: la fórmula en la celda C12 todavía muestra el mismo resultado, es decir, la suma de todos los registros.

Como dije, la fórmula no está dando un resultado incorrecto. De hecho, la función SUM está funcionando bien.

El problema es que hemos utilizado la fórmula incorrecta aquí.

La función SUMA no puede dar cuenta de los datos filtrados y darle el resultado de todas las celdas (ocultas o visibles). Si solo desea obtener la suma / recuento / promedio de las celdas visibles, use las funciones SUBTOTAL o AGREGAR.

Conclusión clave - Comprender el uso correcto y las limitaciones de una función en Excel.

Estas son algunas de las causas comunes que he visto donde su Es posible que las fórmulas de Excel no funcionen o den resultados inesperados o incorrectos. Estoy seguro de que hay muchas más razones por las que una fórmula de Excel no funciona o no se actualiza.

En caso de que conozcas alguna otra razón (tal vez algo que te moleste a menudo), avísame en la sección de comentarios.

¡Espero que hayas encontrado útil este tutorial!