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)

6 comentarios:

  1. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  2. ese calculo no cuadra, si intentas pro ejemplo el percentil 95 o 98 .. excel da 100 pero ami tu formula me da otras cosas....

    ResponderEliminar
    Respuestas
    1. Hola Julio,
      un placer saludarte igualmente.
      Ten en cuenta que el proceso expuesto es manual, y siempre puede tener diferencias con el proceso exacto que produce la función de Excel... y más al trabajar enlos extremos, sobre una tabla de frecuencias acumuladas.
      Se trata de plantear una mecánica y explicación a un proceso de cálculo
      En todo caso no es 'mi fórmula', es la fórmula matemática para el cálculo de percentiles
      ;-)
      Un cordial saludo!!

      Eliminar
  3. Cuándo el resultado de kN/100 me da un número decimal, en qué posición de la columna Frecuencia acumulada debería pararme?

    ResponderEliminar
    Respuestas
    1. Hola Fernando,
      que tal es´tas?, un gusto saludarte igualmente.

      Esa pregunta te la debería responder un estadístico o matemático

      Un cordial saludo

      Eliminar
    2. Deberías seleccionar el superior, pues así te garantiza que los datos están dentro del porcentaje elegido, es decir si coges el Q1 = P25, los datos estarán dentro del 25 %, si o si

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.