Con Excel, dados datos agrupados, ¿cómo estimar cuartiles?

Question

Supongamos que tengo los siguientes datos agrupados para la masa de perros:

Mass         Frequency

0 to 5          13

6 to 10         28

11 to 15        47

16 to 20        21

21 to 25        11

25 to 30         6

¿Cómo puedo usar Excel para estimar el primer cuartil?

Sé cómo hacer una ojiva (usando un diagrama de dispersión con puntos conectados por una curva suave) y puedo usar la ojiva y mis ojos (y tal vez una regla colocada en la pantalla) para encontrar aproximadamente el primer cuartil. ¿Pero Excel puede darme una respuesta más rigurosa?

No quiero utilizar la función "Agregar línea de tendencia", porque una línea de tendencia no es realmente una ojiva (una línea de tendencia no pasa por todos los puntos).

Answer 1

Se trata de datos agrupados, por lo que, a menos que tenga los datos sin procesar, tendrá que hacer algo para recrear la variación en sus datos. Para simplificar, podemos asumir una distribución plana: los perros tienen la misma probabilidad de ser 0 que 5.

Utilice la REPT()función para generar una lista de números separados por comas que representan cada grupo.

=REPT(B2&",",C2)  --- where B2 is your group upper bound and C2 is the group frequency

En la parte inferior, concatena cada una de esas cadenas en una cadena grande:

Copia esta cadena yPegado especialcomoValoresen una nueva hoja de trabajo. Utilizar elTexto a columnasherramienta en elDatospestaña de la cinta para dividir los datos en una columna por valor. Copie todo este rango, luegoPegado especialyTransponerpara convertir esto en una lista vertical de valores. Tus datos deberían verse así:

Siéntase libre de descartar la fila horizontal; no la necesitamos. Ahora queremos interpolar algunos valores en la columna B, usando una fórmula similar a:

=(5*COUNTIF($A$4:A5,A5)/COUNTIF($A$4:$A$5000,A5))+(A5-5)

Desglosando esto tenemos:

    =(5*                         -- your groups are increments of 5
       COUNTIF($A$4:A5,A5)       -- how far down a row is within a group
       /                         
       COUNTIF($A$4:$A$5000,A5)  -- what the frequency is for that group
      )                          
      +(A5-5)                    -- add this result to the lower bound

Ahora, puedes usar la QUARTILE()función en esta lista de valores estimados para aproximar tus cuartiles:

=QUARTILE($B$5:$B$130,1)

Answer 2

Gráficamente quieres hacer unhistogramacon los "bins" establecidos en cuartiles. Puedes buscar cómo hacerlo.

Alternativamente, puedes usar una fórmula. Describiré esto con palabras y podrás resolver los cálculos.

Hay 126 perros. El 25% de eso es 31,5. Quieres encontrar el peso que pesarían menos los 31,5 perros más livianos.

Hay 13 perros en el contenedor más liviano, por lo que se cuentan. Eso deja 18,5 perros, pero el siguiente contenedor tiene 28 perros. Entonces la pregunta es: ¿cuál es el peso del perro número 18,5 más liviano en el segundo contenedor?

Usando lo que se llama interpolación lineal, puedes estimar que el peso desconocido es 18,5/28 entre 6 y 10 libras. Eso es 6+4*(18,5/28).

Puedes utilizar el mismo método para encontrar los otros cuartiles, si es necesario.

El lenguaje aquí es un poco incómodo, pero se entiende la idea. Espero que esto ayude.

EDITAR: Si conoce los pesos de todos los perros individuales, simplemente clasifíquelos en orden y calcule el peso promedio de los perros 31 y 32.