Obtenha mediana no Excel apenas para valores verdadeiros

Question

No meu Excel, faço alguns cálculos para median e average .

Agora, quero receber median para os 20% melhorespercentile dos registros, como (coluna A e B, total de apx 5000 linhas):

3310        TRUE
2250        TRUE
2161        FALSE

A fórmula abaixo está sendo usada para obter os 20% melhorespercentile:

=A1>=PERCENTILE(A1:A5000;0,8)

Agora, como obtenho median dos registros com a coluna B definida como TRUE

Para a média, eu poderia fazer apenas =AVERAGE.IF(B1:B5000;TRUE;A1:A5000) , mas não para mediana.

    

Answer 1

Não há função separada para isso, mas podemos usar uma fórmula aninhada.
Lembre-se de que é uma fórmula de matriz, então use Ctrl, Shift e Enter.

Use isso para o percentil (não uma fórmula de matriz):

=IF(A1>=PERCENTILE($A$1:$A$5000;0,8);"TOP";"NOT")

E isso para TRUE

= MEDIAN(IF(B1:B5000="TOP";A1:A5000))

Isto para FALSE

= MEDIAN(IF(B1:B5000="NOT";A1:A5000))

E lembre-se de Ctrl, Shift e Enter.