Calcula el Porcentaje de Valores en un Rango Específico con Excel

En el análisis de datos, una tarea común es determinar qué proporción de un conjunto de valores se encuentra dentro de un intervalo específico. Esta fórmula ofrece una solución robusta y compatible con la mayoría de las versiones de Excel para calcular el porcentaje de números que caen entre un límite inferior (inclusivo) y un límite superior (exclusivo).

Utiliza una combinación inteligente de las funciones SUMAPRODUCTO y CONTAR, junto con un ingenioso truco conocido como el «doble guion unario» (--), para realizar un recuento condicional y luego calcular su peso sobre el total de valores numéricos.

Sintaxis

=SUMAPRODUCTO(--(rango_de_datos>=limite_inferior),--(rango_de_datos<limite_superior))/CONTAR(rango_de_datos)

Para comprender su funcionamiento, es útil desglosar la fórmula en sus componentes principales:

• SUMAPRODUCTO(…): El motor de la fórmula. SUMAPRODUCTO está diseñada para multiplicar los componentes correspondientes en las matrices dadas y devolver la suma de esos productos. Sin embargo, en este caso, la usamos para contar elementos que cumplen múltiples criterios.
• –(rango_de_datos>=limite_inferior): Esta es la primera condición. La expresión (rango_de_datos>=limite_inferior) crea una matriz de valores lógicos VERDADERO o FALSO. El operador de doble guion unario (--) convierte estos valores lógicos en números: VERDADERO se convierte en 1 y FALSO en 0. El resultado es una matriz de 1s y 0s.
• –(rango_de_datos<limite_superior): De manera similar, esta es la segunda condición que evalúa si los valores son menores que el límite superior, generando otra matriz de 1s y 0s.
• /: El operador de división. Divide el resultado del recuento condicional entre el recuento total.
• CONTAR(rango_de_datos): Esta función calcula el número total de celdas que contienen números en el rango especificado. Actúa como el denominador en nuestro cálculo de porcentaje, representando el 100% del conjunto de datos.

En esencia, SUMAPRODUCTO multiplica las dos matrices de 1s y 0s. Un valor solo se contará (el producto será 1) si ambas condiciones son VERDADERO (1 * 1 = 1). La suma de estos productos es el número total de valores que cumplen ambos criterios.

Ejemplos

Ejemplo 1: Porcentaje de calificaciones en un rango «Notable»

Imaginemos que tenemos una lista de calificaciones de un examen y queremos saber qué porcentaje de alumnos obtuvo una nota entre 7 (inclusive) y 9 (exclusive).

=SUMAPRODUCTO(--(B2:B9>=7),--(B2:B9<9))/CONTAR(B2:B9)

Resultado esperado: 0.375 (o 37.5% si la celda tiene formato de porcentaje).

Desglose del resultado: Hay 3 calificaciones que cumplen los criterios (8.5, 7.0, 7.8) de un total de 8 calificaciones. Por lo tanto, 3 / 8 = 0.375.

Ejemplo 2: Porcentaje de proyectos completados en el segundo trimestre

Esta fórmula también es muy útil con fechas, ya que Excel las trata internamente como números. Supongamos que tenemos una lista de fechas de finalización de proyectos y queremos saber qué porcentaje se completó en el segundo trimestre del año 2023 (del 1 de abril al 30 de junio).

=SUMAPRODUCTO(--(B2:B6>=FECHA(2023,4,1)),--(B2:B6<FECHA(2023,7,1)))/CONTAR(B2:B6)

Resultado esperado: 0.6 (o 60%).

Desglose del resultado: Tres de los cinco proyectos (Beta, Gamma, Epsilon) se completaron en el Q2. Por lo tanto, 3 / 5 = 0.6.

Aplicaciones Prácticas

• Control de CalidadCalcular el porcentaje de piezas fabricadas cuyas medidas se encuentran dentro de las tolerancias aceptadas.
• Análisis FinancieroDeterminar el porcentaje de días en que la variación de una acción se mantuvo en un rango estable (ej. entre -1% y +1%).
• Recursos HumanosCalcular el porcentaje de empleados cuya edad está en un rango determinado para análisis demográficos.
• Investigación de MercadosAnalizar los resultados de una encuesta para saber qué porcentaje de los encuestados dieron una puntuación de satisfacción entre 7 y 9 sobre 10.

Observaciones

Inclusividad de los límites: Tal como está escrita, la fórmula es inclusiva para el límite inferior (>=) y exclusiva para el superior (<). Puedes ajustar fácilmente los operadores según tus necesidades. Por ejemplo, para un rango totalmente inclusivo, usarías >= y <=.

Manejo de celdas vacías o con texto: La función CONTAR solo incluye celdas con valores numéricos en el denominador, lo que evita errores si el rango contiene celdas vacías o texto. La parte de SUMAPRODUCTO también manejará correctamente estos casos, ya que las celdas de texto no cumplirán las condiciones numéricas.

Alternativa al doble guion (--): Aunque el doble guion es el método más común, también puedes forzar la conversión de VERDADERO/FALSO a 1/0 multiplicando por 1. La fórmula quedaría así: =SUMAPRODUCTO((rango_de_datos>=lim_inf)*1,(rango_de_datos<lim_sup)*1)/CONTAR(rango_de_datos).

Errores comunes

• #¡DIV/0!Ocurre si la función CONTAR(rango_de_datos) devuelve cero, es decir, si no hay ninguna celda numérica en el rango especificado.
• Resultados inesperadosAsegúrate de que los límites inferior y superior son correctos y de que los operadores de comparación (<, <=, >, >=) son los adecuados para tu análisis. Un error común es definir un límite inferior mayor que el superior.

Alternativas

En versiones modernas de Excel (2007 y posteriores), existe una alternativa más directa y legible, aunque la fórmula con SUMAPRODUCTO sigue siendo universalmente útil.

• =CONTAR.SI.CONJUNTO(rango_de_datos,">="&limite_inferior,rango_de_datos,"<"&limite_superior)/CONTAR(rango_de_datos)

  Esta fórmula utiliza la función CONTAR.SI.CONJUNTO, que está específicamente diseñada para contar celdas que cumplen múltiples criterios. Su sintaxis puede resultar más intuitiva para muchos usuarios, ya que no requiere el truco del doble guion unario. Los límites deben concatenarse con los operadores usando el símbolo &.