Fórmula do Excel para média excluindo x valores mais altos

Question

Estou procurando a média em uma coluna, em que x linhas são excluídas. Os dados não estão classificados. Os dados a serem excluídos devem ser x linhas inferiores se a coluna foi classificada da menor para a maior.

exemplo dados (coluna) {2; 2; 3; 1; 2}

corte "x" = 2

ordenado {1; 2; 2; 2; 3}

corte as últimas 2 linhas {1; 2; 2}

média = 1.66

    

Answer 1

A solução proposta usa uma coluna auxiliar, mas ela pode estar sempre oculta e não requer intervenção do usuário.

Seus dados estão nas células A1: A5

Em B1 (coluna auxiliar) Coloque essa fórmula e arraste-a para baixo até B5, agora você tem B1 a B5 como lista classificada de A1: A5. Agora, basta ocultar a coluna B.

=SMALL($A$1:$A$5,ROW(1:1))

Na célula C1, o usuário deve colocar o valor do número de corte.

Em D1, ponha a seguinte fórmula

=IF(OR(C1>=ROW(B5),C1=0),"Invalid CutOff",AVERAGE(INDEX(B1:B5,1):INDEX(INDEX(B1:B5,1):B5,MAX(ROW(B1:B5))-C1)))

e de dentro da Barra de Fórmulas, pressione CTRL + SHIFT + ENTER para criar uma fórmula de matriz.

    

Answer 2

Você pode usar essa fórmula se quiser remover apenas 2 (maior número)

=(SUM(A1:A5)-LARGE(A1:A5,1)-LARGE(A1:A5,2))/(COUNT(A1:A5)-2)

seus números estão em A1: A5 Grande (A1: A5,1) é o máximo Grande (A1: A5,2) é o segundo maior número
e assim por diante se você quiser mais
do que você divide a soma pela contagem de números menos (-) a contagem do maior que você quer cortar