martes, 29 de marzo de 2016

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:

Cálculo manual de un percentil con Excel.



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:

Cálculo manual de un percentil con Excel.


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:

Cálculo manual de un percentil con Excel.



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



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:



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)

No hay comentarios:

Publicar un comentario en la entrada