Формула Excel, чтобы показать топ X процент массива

Question

Используя простой набор данных ниже, начиная с A1, с заголовками:

Order   Time
a       1
b       1
c       2
d       4
e       4
f       5
g       6
h       7
i       500
j       600

Я хочу знать, в какое время выполнено% 80 заказов.

В этом примере будет возвращено 7, так как это наибольшее число из восьми, поэтому% 80 заказов выполняется за 7 дней или меньше.

Какую формулу я мог бы использовать, чтобы получить это, учитывая:

1. Временной массив будет содержать 100 секунд времени заказа.
2. Массив времени не всегда будет отсортирован по порядку.
3. Я бы предпочел не использовать вспомогательный столбец или VBA, он должен содержаться в одной ячейке.
4. Я могу использовать формулу массива, просто помните, что массив может различаться по размеру, поэтому массив должен учитывать это.

Я пытался использовать =PERCENTILE.EXC(B2:B11,0.8) но это восстановило значение 401,4

Я также попытался =PERCENTILE.INC(B2:B11,0.8) но это вернул 105,6

Answer 1

Решение довольно простое. Требуется функция SMALL() :

Введите следующую формулу в D2:

=SMALL(B2:B11,ROWS(B2:B11)*0.8)

Обратите внимание, что функция LARGE() также может быть использована, но она не так элегантна:

=LARGE(B2:B11,ROWS(B2:B11)*(1-0.8)+1)