Я ищу среднее значение по столбцу, в результате чего x строк исключаются. Данные не отсортированы. Данные, которые должны быть исключены, должны быть x нижними строками, если столбец отсортирован от самого низкого до самого высокого.
пример данных (столбец) {2; 2; 3; 1; 2}
отсечка "х" = 2
отсортировано {1; 2; 2; 2; 3}
отрезать последние 2 строки {1; 2; 2}
средний = 1,66
В предлагаемом решении используется вспомогательная колонка, но она всегда может быть скрыта и не требует вмешательства пользователя.
Ваши данные в ячейках A1:A5
В B1 (вспомогательный столбец) Поместите эту формулу и перетащите ее вниз до B5. Теперь у вас есть от B1 до B5 в качестве отсортированного списка A1:A5. Теперь просто скройте столбец B.
=SMALL($A$1:$A$5,ROW(1:1))
В ячейке C1 пользователь должен указать значение обрезанного числа.
В D1 положить следующую формулу
=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)))
и из панели формул нажмите CTRL + SHIFT + ENTER, чтобы сделать его формулой массива.
Вы можете использовать эту формулу, если вы хотите удалить только 2 (наибольшее число)
=(SUM(A1:A5)-LARGE(A1:A5,1)-LARGE(A1:A5,2))/(COUNT(A1:A5)-2)
ваши цифры в A1:A5 Large(A1:A5,1) это максимум
Большой (A1:A5,2) является вторым по величине числом
и так далее, если вы хотите больше
чем вы делите сумму на количество чисел минус (-) количество наибольшее, которое вы хотите обрезать