Cálculo manual de un percentil con Excel.

Hablábamos el otro día en un grupo de compañeros sobre el proceso de cálculo que realiza Excel para el cálculo de los percentiles y cómo devuelve un valor la función PERCENTIL o alguna de sus variantes, por ejemplo =PERCENTIL.INC(...)

Así tiramos de Internet y concluimos que no debía ser muy distinto al proceso manual de cálculo... que expongo a continuación.

Partiremos de un rango de diez valores:

Obviamente obtener el percentil 50% y 80% es muy sencillo empleando las funciones de la imagen (celdas E2 y E4):
=PERCENTIL.INC($B$2:$B$11;D2)
que retorna el valor 57,50
y también
=PERCENTIL.INC($B$2:$B$11;D4)
que retorna el valor 96,00.

Pero, y el cálculo manual, ¿cómo sería?.
El inicio es construir una tabla de frecuencias y frecuencias acumuladas a partir de nuestro rango origen en B2:B11:

Para el cálculo de la frecuencia bastaría emplear una función CONTAR.SI, y para la frecuencia acumulada una SUMA incremental.

Para llegar al valor del percentil emplearemos la siguiente función:

Veamos el proceso de cálculo para el percentil 50% de esos 10 valores.

haz clic en la imagen

Tenemos ciertas variables a calcular:
N (número de valores):= 10
k (porcentaje de casos del percentil):= 50
kN/100:= 50x10/100=5

este valor cinco es el que buscaremos en la columna de frecuencia acumulada, al comprobar que la frecuencia del dato es mayor que uno, aplicaremos un promedio entre ese dato y el siguiente (entre 50 y 65), esto es:
Li (Límite inferior de la puntuación donde se haya el percentil):= =PROMEDIO(A17:A18) = 57,50
fi (frecuencia de la puntuación donde se haya el percentil):= 1
fa (frecuencia acumulada hasta el límite inferior de la puntuación donde se encuentre el percentil):= 5
según aparece en la imagen anterior...

Finalmente el cálculo de percentil al 50% lo incluimos en la celda F19:
=F14+(1/F15*F18-F17)
= 57,50
que coincide, no puede ser de otra forma, con la función
=PERCENTIL.INC($B$2:$B$11;D2)

Si repetimos el cálculo para el percentil 80% sobre los mismos 10 valores:

haz clic en la imagen

Tenemos ciertas variables a calcular:
N (número de valores):= 10
k (porcentaje de casos del percentil):= 80
kN/100:= 80x10/100=8

este valor cinco es el que buscaremos en la columna de frecuencia acumulada, al comprobar que la frecuencia del dato es uno, nos quedaremos con ese valor, esto es:
Li (Límite inferior de la puntuación donde se haya el percentil):= 95
fi (frecuencia de la puntuación donde se haya el percentil):= 1
fa (frecuencia acumulada hasta el límite inferior de la puntuación donde se encuentre el percentil):= 7
según aparece en la imagen anterior...

Finalmente el cálculo de percentil al 80% lo incluimos en la celda F30:
=F25+(1/F26*F29-F28)
= 96,00
que coincide, no puede ser de otra forma, con la función
=PERCENTIL.INC($B$2:$B$11;D4)