Pregunta La fórmula de Excel para el promedio excluyendo x los valores más altos


Estoy buscando el promedio sobre una columna, por lo que x líneas están excluidas. Los datos no están ordenados Los datos que se excluirán deben ser las líneas de fondo x si la columna se ordenó de menor a mayor.

ejemplo datos (columna) {2; 2; 3; 1; 2}

corte "x" = 2

ordenados {1; 2; 2; 2; 3}

cortar las últimas 2 líneas {1; 2; 2}

promedio = 1.66


0


origen


¿Estás bien con una columna de ayuda? ¿Sus datos de columna siempre comienzan en la fila 1 o pueden comenzar en cualquier fila? - pat2015
No debería haber una columna auxiliar. Al menos no en el sentido de que es visible para el usuario y agrega pasos adicionales que el usuario debe hacer. - Benjamin Hastings
para todos los efectos, siempre comienza en la celda A1 y termina en A5 - Benjamin Hastings
¿Puede el corte ser el mismo que el número total de filas, sin embargo, no será significativo? ¿Vas a poner el número de corte en la misma celda? - pat2015
No es necesario tener en cuenta situaciones "extrañas" en las que el límite es igual o mayor que el número de filas Sí, el punto de corte alias "x" será un número tomado de una celda, digamos B1 - Benjamin Hastings


Respuestas:


La solución propuesta utiliza una Columna auxiliar, pero puede ocultarse siempre y no requerirá la intervención del usuario.

Sus datos están en las celdas A1: A5

En B1 (Columna de ayuda) Coloque esta fórmula y arrástrela hacia abajo hasta B5. Ahora tiene B1 a B5 como una lista ordenada de A1: A5. Ahora simplemente oculta la columna B.

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

En la celda C1, el usuario debe poner el valor del número de corte.

En D1 pon la siguiente 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)))

y desde dentro de la barra de fórmulas, presiona CTRL + MAYÚS + ENTRAR para convertirla en una fórmula de matriz.

enter image description here


1



con la tabla de ayuda esto funciona muy bien. ¡Gracias! - Benjamin Hastings


Puede usar esta fórmula si quiere eliminar solo 2 (número más grande)

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

tus números están en A1: A5 Grande (A1: A5,1) es el máximo
Grande (A1: A5,2) es el segundo número más grande
y así sucesivamente si quieres más
de lo que divide la suma por el recuento de números menos (-) el recuento de mayor que desea cortar


0



Necesita ser dinámico. La fórmula busca un valor de celda, y ese valor es 15, la fórmula eliminará 15 filas. - Benjamin Hastings