A função quartil do Excel não funciona

Question

Estou tentando descobrir como fazer com que as funções estatísticas do Excel funcionem para mim, mas elas estão descartando números aparentemente aleatórios quando uso conjuntos de dados de contagem uniforme.

Por exemplo, vamos usar este conjunto de dados: {4,6,8,12,14,16}

Nesta amostra, os valores corretos são:

• mediana: 10
• q1: 6
• q3: 14

No entanto, quando você usa o Excel, obtém os seguintes resultados com median() e quartile.exc() :

• mediana: 10
• q1: 5.5
• q3: 14,5

Estou abordando isso incorretamente ou o Excel simplesmente não está equipado para lidar com quartis de contagem igual?

    

Answer 1

A breve explicação é que o Excel está calculando os quartis como percentis. Isso é realmente muito diferente da forma como normalmente pensamos em quartis (como medianas da metade superior / inferior dos dados). Aqui está uma explicação rápida de como o Excel faz o que faz, usando seus dados como exemplo. Não posso ter 100% de certeza de que esse é o algoritmo exato usado pelo Excel, mas isso fornecerá os mesmos resultados.

1. O Excel atribui PERCENTILES a cada valor na matriz.
   P (4) = 0; P (6) = 0,20; P (8) = 0,40; ...; P (16) = 1

2. O Excel verifica o percentil solicitado na matriz. No Q1, 0,25 fica entre 6 e 8.

3. O Excel interpola linearmente entre esses valores com base no percentil.
   O percentil 0,25 é 0,05 percentil maior que o percentil 0,20.
   0,05 / (P (8) -P (6)) = 0,05 / 0,20 = 1/4
   Portanto, o 25º percentil é 1/4 entre 6 e 8. Assim, 6,5 é o valor retornado. (Eu percebi que você digitou 5.5, mas eu verifiquei seus dados no Excel, e 6.5 é retornado quartil. Da mesma forma, 13,5 é retornado para Q3 em vez de 14,5.)

Isto, obviamente, é uma maneira estranha de calcular um quartil e não é encontrado na página da Wikipedia sobre quartis.

Agora, para encontrar um quartil do jeito que você quiser - tenho duas sugestões.

1. Tente o Add-in do Pacote de Estatísticas. Eu não o tenho instalado aqui no meu computador de trabalho, mas vale a pena tentar ver se ele retorna valores quartil diferentes daqueles retornados pela função de planilha.

2. Você pode usar uma fórmula de substituto hackeado. É confuso, mas acho que ele captura o que você está procurando.

Para o primeiro trimestre, você pode usar:

=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 o terceiro trimestre, você pode usar:

=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)))

    

Answer 2

A função de quartil incorporada do Excel usa a interpolação para calcular os quartis. Bem, como ele encontra 5.5 e 14.5 no seu exemplo? Dado que o tamanho da sua amostra (n) é 6, ele calcula o primeiro quantil da seguinte forma:

 = (n + 1) / 4 = 7 / 4 = 1.75

Quando 1.75 cai entre os valores de 1 e 2, o Excel interpola os dados para produzir o resultado 5.5.

Calcula o terceiro quantil da seguinte forma:

 = 3 * (n + 1) / 4 = 21 / 4 = 5.25

Com 5,25 entre os valores de 5 e 6, o Excel interpola os dados para produzir o resultado 14.5.

Uma macro simples pode ser escrita para alcançar os resultados desejados. Usar a função ROUND() para os valores acima de 1,75 e 5,25 gerará Q1 e Q3 como segundo e quinto elementos do conjunto de dados, ou seja, 6 e 14.

Quanto ao motivo pelo qual o Excel se comporta dessa maneira, não há acordo universal quanto à escolha dos valores de quartil . O Excel usa o Método 2 , enquanto você usa Método 1 no seu exemplo.

    

Answer 3

O Excel 2010 introduziu o QUARTILE.INC e o QUARTILE.EXC.

QUARTILE.INC é o mesmo que a antiga função QUARTILE do Excel e interpola em uma base N-1, enquanto QUARTILE.EXC corresponde à função usada no Minitab e alguns outros pacotes de estatísticas e interpola em uma base N + 1.

Observe que nenhum desses fornece os valores esperados. Interpolar em uma base N faria isso, mas você provavelmente está pensando no método original de Tukey, que é um dos vários métodos de "Dobradiça" para determinar os quartis.

Se você quiser ler mais, eu escrevi um extenso tutorial sobre o cálculo de quartis, Quartis para Box Plots , com ênfase no uso do Excel. O artigo da Wikipedia citado em outra parte deste tópico é bastante simplista.