Com o Excel, dados agrupados, como estimar quartis?

Question

Suponha que eu tenha os seguintes dados agrupados para massa de cães:

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

Como posso usar o Excel para estimar o primeiro quartil?

Eu sei fazer uma ogiva (usando um diagrama de dispersão com pontos conectados por uma curva suave), e posso usar a ogiva e meus olhos (e talvez uma régua colocada na tela) para encontrar o primeiro quartil. Mas o Excel pode me dar uma resposta mais rigorosa?

Eu não quero usar a função "Adicionar linha de tendência", porque uma linha de tendência não é realmente uma ogiva (uma linha de tendência não passa por todos os pontos).

    

Answer 1

Esses dados são agrupados, portanto, a menos que você tenha os dados brutos, terá que fazer algo para recriar a variação em seus dados. Para simplificar, podemos supor uma distribuição plana - os cães têm a mesma probabilidade de ser 0 e 5.

Use a função REPT() para gerar uma lista de números, separados por vírgulas, representando cada grupo.

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

Naparteinferior,concatenecadaumadessasstringsemumastringgrande:

CopieestastringeColeespecialcomoValoresemumanovaplanilha.UseaferramentaTextoparacolunasnaguiadafaixadeopçõesDadosparadividirosdadosemumacolunaporvalor.Copietodoesteintervalo,depois,ColarespecialeTranspor,paracolocá-lonumalistaverticaldevalores.Seusdadosdevemserparecidoscomisto:

Sinta-seàvontadeparadescartaralinhahorizontal-nãoprecisamosdela.AgoraqueremosinterpolaralgunsvaloresnacolunaB,usandoumafórmulacomo:

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

Quebrandoisso,temos:

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

Agora,vocêpodeusarafunçãoQUARTILE()nessalistadevaloresestimadosparaaproximarseusquartis:

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

    

Answer 2

Graficamente, você quer fazer um histograma com as "bandejas" definidas para quartis. Você pode procurar como fazer isso.

Como alternativa, você pode usar uma fórmula. Vou descrever isso em palavras, e você pode descobrir a matemática.

Existem 126 cachorros. 25% disso é 31,5. Você quer encontrar o peso que os 31,5 cães mais leves pesariam menos que.

Existem 13 cães na sua lixeira mais leve, para que sejam contados. Isso deixa 18,5 cachorros, mas o próximo bin tem 28 cachorros. Então a questão é: qual é o peso do 18.5º cão mais leve no segundo compartimento?

Usando o que é chamado de interpolação linear, você pode estimar que o peso desconhecido é 18,5 / 28 do caminho entre 6 e 10 libras. Isso é 6 + 4 * (18,5 / 28).

Você pode usar a mesma abordagem para encontrar os outros quartis, se precisar.

A linguagem aqui é um pouco estranha, mas você tem a ideia. Espero que isso ajude.

EDIT: Se você conhece os pesos de todos os cães individuais, apenas classifique-os em ordem e calcule o peso médio dos cães 31 e 32.