Fórmula PERCENTIL SI – Excel y Google Sheets
Descargar el Libro de Ejemplo
Este tutorial demostrará cómo calcular el «Percentil Si», recuperando el percentil késimo de sólo los valores que cumplen ciertos criterios.
Función PERCENTIL
La función PERCENTIL se utiliza para calcular el percentil késimo de los valores de un rango donde k es el valor del percentil entre 0 y 1 inclusive.
=PERCENTIL($D$3:$D$11;0.75)
Para crear un «Percentil Si», utilizaremos la función PERCENTIL junto con la función SI en una fórmula de matriz.
PERCENTIL SI
Combinando el PERCENTIL y el SI en una fórmula de matriz, podemos crear esencialmente una función «PERCENTIL SI» que funciona de manera similar a la función incorporada PROMEDIO.SI. En este ejemplo, tenemos una lista de puntuaciones obtenidas por los estudiantes en dos asignaturas diferentes:
Queremos encontrar los percentiles 75 de las puntuaciones obtenidas en cada asignatura de esta manera:
Para conseguirlo, podemos anidar una función SI con la materia como criterio dentro de la función PERCENTIL así:
=PERCENTIL(SI(<rango de criterios>=<criterios>, <rango de valores>),<percentil>)=PERCENTIL(SI($C$3:$C$11=$F3;$D$3:$D$11);0.75)
Cuando se utiliza Excel 2019 y anteriores, se debe introducir la fórmula de la matriz presionando CTRL + SHIFT + ENTER (en lugar de ENTER), indicando a Excel que la fórmula en una fórmula de matriz. Sabrás que es una fórmula de matriz por las llaves que aparecen alrededor de la fórmula (ver imagen superior). ¿Cómo funciona la fórmula? La función If evalúa cada celda de nuestro rango de criterios como VERDADERO o FALSO, creando dos matrices:
=PERCENTIL(SI({VERDADERO;FALSO;FALSO;VERDADERO;FALSO;VERDADERO;FALSO;VERDADERO;FALSO};{0.99;0.8;0.93;0.42;0.87;0.63;0.71;0.58;0.73});0.75)
A continuación, la función SI crea una única matriz, sustituyendo cada valor por FALSO si no se cumple su condición.
=PERCENTIL({0.99;FALSO;FALSO;0.42;FALSO;0.63;FALSO;0.58;FALSO};0.75)
Ahora la función PERCENTILE se salta los valores FALSE y calcula el percentil 75 de los valores restantes (0,72 es el percentil 75).
PERCENTIL SI con múltiples criterios
Para calcular el PERCENTIL SI con múltiples criterios (de forma similar a como funciona la función incorporada PROMEDIO.SI.CONJUNTO), puede simplemente multiplicar los criterios juntos:
=PERCENTIL(SI((<criterio1 rango>=<criterio1>)*(<criterio2 rango>=<criterio2>),<valores rango>),<percentil>){=PERCENTIL(SI(($D$3:$D$11=$H3)*($C$3:$C$11=$G3);$E$3:$E$11);0.75)}
Otra forma de incluir múltiples criterios es anidar más sentencias SI dentro de la fórmula.
Consejos y trucos:
- Siempre que sea posible, haga referencia a la posición (k) de una celda de ayuda y bloquee la referencia (F4), ya que esto facilitará el autorellenado de las fórmulas.
- Si está utilizando Excel 2019 o más reciente, puede introducir la fórmula sin CTRL + SHIFT + ENTER.
- Para recuperar los nombres de los alumnos que obtuvieron las mejores calificaciones, combine esto con INDICE / COINCIDIR.
PERCENTIL SI en Google Sheets
La fórmula PERCENTIL SI funciona exactamente igual en Google Sheets que en Excel, salvo que debes rodear la fórmula con la función ARRAYFORMULA para indicar a Google Sheets que se trata de una fórmula de matriz.
