Solución:
La breve explicación es que Excel calcula los cuartiles como percentiles. Esto es realmente muy diferente de la forma en que normalmente pensamos en los cuartiles (como medianas de la mitad superior / inferior de los datos). Aquí hay una explicación rápida de cómo Excel hace lo que hace, usando sus datos como ejemplo. No puedo estar 100% seguro de que este sea el algoritmo exacto que usa Excel, pero esto dará los mismos resultados.
-
Excel asigna PERCENTILES a cada valor de la matriz.
P (4) = 0; P (6) = 0,20; P (8) = 0,40; …; P (16) = 1
-
Excel luego verifica dónde cae el percentil solicitado en la matriz. Para Q1, 0.25 cae entre 6 y 8.
-
Excel luego interpola linealmente entre estos valores según el percentil.
El percentil 0,25 es un percentil 0,05 superior al percentil 0,20.
0,05 / (P (8) -P (6)) = 0,05 / 0,20 = 1/4
Entonces, el percentil 25 es 1/4 del camino entre 6 y 8. Por lo tanto, 6.5 es el valor devuelto. (Me doy cuenta de que escribió 5.5, pero verifiqué sus datos en Excel y 6.5 es el cuartil devuelto. Del mismo modo, se devuelve 13.5 para Q3 en lugar de 14.5).
Por supuesto, esta es una forma extraña de calcular un cuartil.
Ahora, para encontrar un cuartil de la forma que desee, tengo dos sugerencias.
-
Pruebe el complemento del paquete de estadísticas. No lo tengo instalado aquí en esta computadora, pero vale la pena intentarlo para ver si devuelve valores de cuartiles diferentes a los devueltos por la función de la hoja de trabajo.
-
Puede utilizar una fórmula sustituta pirateada. Es desordenado, pero creo que captura lo que estás buscando.
Para el primer trimestre, puede utilizar:
=IF(ISEVEN(ROUNDDOWN(COUNT(A1:A8)/2,0)),AVERAGE(SMALL(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2),SMALL(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2+1)),SMALL(A1:A8,ROUNDUP(ROUNDDOWN(COUNT(A1:A8)/2,0)/2,0)))
Para el tercer trimestre, puede utilizar:
=IF(ISEVEN(ROUNDDOWN(COUNT(A1:A8)/2,0)),AVERAGE(LARGE(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2),LARGE(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2+1)),LARGE(A1:A8,ROUNDUP(ROUNDDOWN(COUNT(A1:A8)/2,0)/2,0)))
La función de cuartiles incorporada de Excel utiliza la interpolación para calcular los cuartiles. Bueno, ¿cómo encuentra 5.5 y 14.5 en tu ejemplo? Dado que el tamaño de su muestra (n) es 6, calcula el primer cuantil de la siguiente manera:
= (n + 1) / 4 = 7 / 4 = 1.75
Como 1,75 se encuentra entre los valores de 1 y 2, Excel interpola los datos para producir el resultado 5,5.
Calcula el tercer cuantil de la siguiente manera:
= 3 * (n + 1) / 4 = 21 / 4 = 5.25
Como 5.25 cae entre los valores de 5 y 6, Excel interpola los datos para producir el resultado 14.5.
Se puede escribir una macro simple para lograr los resultados que desea. Utilizando el ROUND()La función para los valores anteriores de 1,75 y 5,25 generará Q1 y Q3 como segundo y quinto elementos de su conjunto de datos, es decir, 6 y 14.
En cuanto a por qué Excel se comporta así, no existe un acuerdo universal sobre la elección de los valores de los cuartiles. Excel usa el Método 2 mientras que usted usa el Método 1 en su ejemplo.
Excel 2010 introdujo QUARTILE.INC y QUARTILE.EXC.
CUARTIL.INC es lo mismo que la antigua función CUARTIL de Excel y se interpola sobre una base N-1, mientras que CUARTIL.EXC coincide con la función utilizada en Minitab y algunos otros paquetes de estadísticas e interpola sobre una base N + 1.
Tenga en cuenta que ninguno de estos proporciona los valores que esperaba. La interpolación sobre una base N lo haría, pero probablemente esté pensando en el método Tukey original, que es uno de varios métodos de “bisagra” para determinar los cuartiles.
Si desea leer más, escribí un tutorial extenso sobre el cálculo de cuartiles, Quartiles for Box Plots, con énfasis en el uso de Excel. El artículo de Wikipedia citado en otra parte de este hilo es bastante simplista.