Cómo calcular percentiles en Excel

Cómo calcular percentiles en Excel

Excel ofrece dos funciones principales para calcular percentiles: PERCENTILE.INC() y PERCENTILE.EXC().

En sentido estricto, también existe una tercera función,PERCENTILE(), que se utilizaba en versiones antiguas del software (Excel 2007 y anteriores) y que ahora se mantiene por motivos de compatibilidad. Esta antigua función funciona de la misma manera que PERCENTILE.INC().

En este tutorial, nos centraremos en las funciones más recientes:PERCENTILE.INC() y PERCENTILE.EXC().

Función PERCENTILE.INC() de Excel

Sintaxis

PERCENTILE.INC(array, k)

donde:

• array—el arreglo o rango de datos para los que se calcula el percentil,

• k—el valor percentil en el rango de 0 a 1, ambos inclusive.

Se requieren ambos argumentos.

La función PERCENTILE.INC() devuelve el percentil k-ésimo de los valores de un rango, donde k está en el rango de 0 a 1, incluidos los valores 0 y 1. En otras palabras, esta función incluye el primer y último valor del arreglo.

Algoritmo

En segundo plano, la función Excel « PERCENTILE.INC() » realiza los siguientes pasos:

1. Ordena los valores del arreglo proporcionado en orden ascendente.
2. Determina el valor percentil basado en el argumento k y el número N de puntos de datos en el arreglo utilizando la fórmula: k(N-1) + 1.
3. Si el resultado del paso anterior no es un número entero, el algoritmo interpola entre los valores más cercanos del arreglo.

Ejemplo

Supongamos que queremos calcular el percentil 30 para un arreglo de números enteros del 1 al 10, ambos inclusive, utilizando la función de Excel « PERCENTILE.INC() » (Porcentil). Así es como se verá nuestra fórmula:

Nota: Aunque los valores del arreglo anterior están ordenados con fines ilustrativos, en realidad no es necesario ordenarlos previamente, ya que el algoritmo lo hará por nosotros en segundo plano.

A continuación se muestra el resultado de nuestro cálculo:

Posibles errores

• #NUM!— cuando el arreglo proporcionado está vació, o k < 0, o k > 1.

• #VALUE!—cuando k no es numérico.

Función PERCENTILE.EXC() de Excel

Sintaxis

PERCENTILE.EXC(array, k)

donde:

• array—el arreglo o rango de datos para los que se calcula el percentil,

• k—el valor percentil en el rango de 0 a 1, exclusivo.

Se requieren ambos argumentos.

La función PERCENTILE.EXC() devuelve el percentil k-ésimo de los valores de un rango, donde k está comprendido entre 0 y 1, excluyendo los valores 0 y 1. Más concretamente, para un arreglo con N puntos de datos, el valor de k debe satisfacer las dos condiciones siguientes:

1. K > 1/(N+1)
2. K < N/(N+1)

En pocas palabras, la función PERCENTILE.EXC() solo define percentiles para probabilidades estrictamente comprendidas entre 0 y 1. Esto significa que no calcula los valores del mínimo absoluto (percentil 0) ni del máximo absoluto (percentil 100), aunque todos los puntos de datos se siguen utilizando en la interpolación.

Algoritmo

En segundo plano, la función Excel « PERCENTILE.EXC() » realiza los siguientes pasos:

1. Ordena los valores del arreglo proporcionado en orden ascendente.
2. Determina el valor percentil basado en el argumento k y el número N de puntos de datos en el arreglo utilizando la fórmula: k(N+1).
3. Si el resultado del paso anterior no es un número entero, el algoritmo interpola entre los valores más cercanos del arreglo.

Ejemplo

Supongamos que queremos calcular el percentil 30 para un arreglo de números enteros del 1 al 10, ambos inclusive, utilizando la función de Excel « PERCENTILE.EXC() » (percentil). Así es como se verá nuestra fórmula:

Nota: Aunque los valores del arreglo anterior están ordenados con fines ilustrativos, en realidad no es necesario ordenarlos previamente, ya que el algoritmo lo hará por nosotros en segundo plano.

A continuación se muestra el resultado de nuestro cálculo:

Posibles errores

• #NUM!— cuando el arreglo proporcionado está vació, o k <= 1/(N+1), o k >= N/(N+1).

• #VALUE!—cuando k no es numérico.

Para aprender u actualizar otras funciones populares de Excel, consulta este tutorial: Las 15 fórmulas básicas de Excel que todo el mundo debería conocer.

PERCENTILE.INC CONTRA. PERCENTILE.EXC en Excel

Hasta ahora, podemos ver claramente muchas similitudes entre las funciones de Excel « PERCENTILE.INC() » y « PERCENTILE.EXC() ». Sin embargo, también hay diferencias claras entre ustedes que los convierten en dos funciones distintas y dan lugar a resultados diferentes. Recapitulemos en qué se parecen y en qué se diferencian las funciones PERCENTILE.INC() y PERCENTILE.EXC() de Excel.

Similitudes:

1. Sintaxis de la función.

2. Lógica general del algoritmo (excepto la fórmula utilizada para determinar el valor percentil).

3. Posibles errores (#NUM! cuando el arreglo está vacía o k está fuera de su rango posible y #VALUE! cuando k no es numérico).

Diferencias:

1. El rango posible para el argumento k:

• PERCENTILE.INC()—de 0 a 1, ambos inclusive.

• PERCENTILE.EXC()—de 1/(N+1) a N/(N+1), ambos inclusive.

2. Tratamiento de los valores de un arreglo:

• PERCENTILE.INC()—incluye el primer y el último valor.

• PERCENTILE.EXC()—no define percentiles en los primeros y últimos valores (0 % y 100 %), pero incluye todos los datos en su interpolación.

3. Fórmula para determinar el valor percentil:

• PERCENTILE.INC()—k(N-1) + 1.

• PERCENTILE.EXC()—k(N+1).

Veamos las funciones de Excel « PERCENTILE.INC() » y « PERCENTILE.EXC() » en acción aplicándolas al mismo arreglo de números enteros del 1 al 10, ambos inclusive, y jugando con diferentes valores de k:

La tabla anterior muestra que la función PERCENTILE.EXC() no está definida para valores de k dentro o fuera de su rango válido (k ≤ 1/(N+1) o k ≥ N/(N+1)). Esto no es un defecto. Refleja la definición matemática de percentiles exclusivos. Las pequeñas diferencias entre los resultados de .INC() y .EXC() cerca de los extremos se deben a la forma en que cada función interpola las posiciones percentiles.

Por otro lado, debemos tener en cuenta que, en la gran mayoría de los casos, los valores demasiado bajos o demasiado altos de k no tienen ningún valor particular para vos desde el punto de vista estadístico. Estas limitaciones rara vez suponen un problema en la práctica. Además, en la vida real, normalmente necesitamos calcular percentiles para arreglos mucho más grandes que el de nuestro ejemplo anterior, lo que mitiga las diferencias en los resultados.

En la mayoría de los casos, tiene más sentido optar por la función Excel « PERCENTILE.INC() », que también coincide con las fórmulas predeterminadas utilizadas para calcular percentiles en muchos lenguajes de programación, incluidos Python y R.

Sin embargo, si queremos evitar definir percentiles en los extremos del conjunto de datos, la función PERCENTILE.EXC() es la opción adecuada. No calculará los resultados para el percentil 0 ni el 100, pero seguirá utilizando todos los puntos de datos al interpolar entre rangos.

Es importante disponer de datos limpios y representativos para poder extraer de ellos información valiosa para el negocio. El curso Preparación de datos en Excel y la hoja de referencia Manipulación de datos en Excel son recursos útiles para aprender a preparar de manera eficiente tus datos para su posterior análisis.

Conclusión

Hemos analizado la teoría estadística esencial que subyace a los percentiles, su definición, las principales áreas de aplicación y las dos funciones principales que se utilizan para calcular percentiles en Excel, incluyendo su sintaxis, algoritmos, ejemplos de cálculo y posibles errores.

También hemos comparado las dos funciones de Excel, hemos destacado sus similitudes y diferencias importantes, hemos puesto en práctica ambas funciones en el mismo arreglo de datos para comparar los resultados y hemos aprendido cuál es el mejor escenario para cada una de ellas.

Para aprovechar todo el potencial de Excel, considera la posibilidad de realizar un programa básico de Excel Fundamentals, apto para principiantes, que te ayudará a aprender, a través de ejercicios prácticos, tanto las funciones y fórmulas básicas como las avanzadas que se utilizan para la preparación, visualización y análisis de datos en Excel.