Como posso estimar o enésimo valor (ou o percentual Nth) de uma distribuição de frequência no Excel?

Question

Eu tenho uma planilha do Excel contendo contagens de frequência em vez de dados brutos. Eu gostaria de poder encontrar coisas como o 653º valor ou o 95º percentil facilmente.

Como exemplo, digamos que os dados brutos são latência por pacote. Os dados da contagem de frequência que tenho podem parecer:

1ms = 1234567
3ms = 34254
5ms = 33034
7ms = 6901
10ms = 76

6901 pacotes caíram no balde "mais de 5 milissegundos, mas não mais que 7 milissegundos".

Como os buckets são bastante granulares, eu estava pensando em fazer alguns truques para calcular algumas informações estatísticas padrão, como se eu tivesse os dados brutos. O cálculo da média não exige isso, mas algo como mediana ou desvio padrão, ou 99º percentil, todos dependem de um intervalo de dados. Alguém pode ajudar?

    

Answer 1

Para as freqüências que você tem, o Excel não possui linhas suficientes para armazenar os dados brutos. Reduzi as frequências por um fator de 100 (exceto a última) para que elas se encaixassem em uma planilha do Excel. Se você executar esse código, deverá receber dados brutos que, quando usados com uma fórmula como

{=FREQUENCY($A$2:$A$13095,{1,3,5,7,10})}

dá a você (minhas freqüências reduzidas) 12345, 343, 330, 69, 7. Ele produz números aleatórios dentro de seus intervalos.

Sub MakeRawData()

    Dim i As Long
    Dim dRaw As Double

    For i = 1 To 12345
        dRaw = Rnd
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 343
        dRaw = (2 - 1 + 1) * Rnd + 1
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 330
        dRaw = (4 - 3 + 1) * Rnd + 3
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 69
        dRaw = (6 - 5 + 1) * Rnd + 5
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 7
        dRaw = (9 - 7 + 1) * Rnd + 7
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

End Sub

Não é muito eficiente e pode levar vários minutos para ser executado, mas se você não precisa fazer isso com frequência, isso não deve ser um grande problema.

    

Answer 2

Estou cerca de 1,5 anos atrasado para a festa, mas pensei em postar apenas para qualquer outra pessoa que se deparasse com isso.

Acho que sua melhor aposta é interpolar uma função de distribuição cumulativa dos dados que você tem. Isso pode exigir um pouco de sutileza e acenos de mão, especialmente se os dados de amostra que você forneceu estiverem próximos da distribuição com a qual você está trabalhando. No entanto, com certeza é melhor criar (e armazenar!) Mais de um milhão de pontos de dados falsos de uma distribuição que você está apenas adivinhando em primeiro lugar.

Para obter o CDF, você precisará calcular a probabilidade cumulativa para cada depósito. Suponho que sempre haja alguma latência, portanto, leve 0 como seu valor mínimo com uma frequência de 0. Para encontrar o valor de CDF em cada limite superior do intervalo, use a fórmula

(sum of frequencies in bucket and previous buckets)/(sum of all frequencies)

Para os dados de amostra fornecidos, os pontos de probabilidade cumulativos seriam

{(0,0); (1;0.943); (3,0.969); (5,0.995); (7,0.999); (10,1)}

Agora, para o refinamento. Se você tiver algumas percepções de bom senso sobre a aparência da distribuição, por exemplo, Você acha que em algum lugar em torno de 25% das latências são menores que 0,1 ms, você pode adicionar essas intuições aos seus dados. Se você não tem ideia de como a distribuição deve ser, então você pode simplesmente rolar com o que tem.

A partir daqui, você tem duas opções: (a) interpolar linearmente entre os pontos que possui ou (b) ajustar uma forma funcional, como uma distribuição beta, aos seus dados. (a) é mais simples porque não requer regressão; no entanto, ele não lhe dará uma imagem mais refinada do que a que você já tem, e o cálculo de frequências requer um pouco de kung fu da fórmula do Excel. (b) fornecerá a você uma imagem refinada que mais provavelmente se assemelha aos dados subjacentes mais de perto que a interpolação linear, e requer apenas fórmulas simples e simples do Excel para obter freqüências para qualquer intervalo ou percentil; no entanto, requer uma regressão, que requer o suplemento Solver. Eu prefiro a opção (b) porque ela lhe dá o maior retorno possível (ex. Esforço).