Как использовать условный массив с более чем одним условием для вычисления процентилей?

Question

У меня есть таблица в Excel со следующими данными:

+-------------------+----------------------+----------+
| Contribution Type | % Contribution Match | % Salary |
+-------------------+----------------------+----------+
| Type 1            |                  0.5 |          |
| Type 1            |                  0.6 |          |
| Type 1            |                      |          |
| Type 2            |                      |     0.03 |
| Type 2            |                      |     0.04 |
| Type 2            |                      |        0 |
| Type 3            |                  0.7 |     0.05 |
| Type 3            |                  0.6 |     0.04 |
| Type 3            |                      |     0.05 |
| Type 1            |                  0.5 |          |
| Type 2            |                      |     0.04 |
| Type 3            |                 0.75 |      0.1 |
+-------------------+----------------------+----------+

Я хотел бы использовать формулу массива для расчета квартилей для каждого типа вклада на основе дополнительного условия (поскольку тип вклада является первым условием), что соответствующие значения данных не оставляются пустыми или вводятся как нулевое значение.

Тип 1 соответствует 100% заработной платы до определенного процента (X) вклада работников:

{= PERCENTILE.EXC(ЕСЛИ (Взносы [Тип взноса] = «Тип 1», Взносы [% соответствия взносов]), 0,25)} (и т.д. Для med, avg и 75th)

Тип 2 соответствует ограниченному проценту (Y) заработной платы без ограничения вклада работника:

{= PERCENTILE.EXC(IF(Взносы [Тип взноса] = «Тип 2», Взносы [% зарплата]), 0,25)}(и т.д. Для med, avg и 75th)

Тип 3 имеет ограничения как на вклад сотрудника, так и на процент заработной платы:

{= PERCENTILE.EXC(ЕСЛИ (взносы [тип взноса] = «тип 3», взносы [% соответствия взносов]), 0,25)} (и т.д. Для med, avg и 75-й)

{= PERCENTILE.EXC(IF (Взносы [Тип взноса] = «Тип 3», Взносы [% зарплата]), 0,25)} (и т.д. Для med, avg и 75th)

Полученная таблица вычисляет квартили, включая пустые и нулевые значения (не то, что я хочу):

+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+
|         Percentage of Employee Contribution        |            Percentage of Salary           |
+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+
|        | 25th %-ile| Median | Average | 75th %-ile | 25th %-ile| Median | Average | 75th %-ile |
| Type 1 | 12.50%    | 50.00% | 40.00%  | 57.50%     | 0.00%     | 0.00%  | 0.00%   | 0.00%      |
| Type 2 | 0.00%     | 0.00%  | 0.00%   | 0.00%      | 0.75%     | 3.50%  | 2.75%   | 4.00%      |
| Type 3 | 15.00%    | 65.00% | 51.25%  | 73.75%     | 4.25%     | 5.00%  | 6.00%   | 8.75%      |
+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+

Я пробовал каждую комбинацию IF(И ЕСЛИ (ЕСЛИ я могу придумать). Я относительно новичок в использовании формул массива, поэтому любая помощь будет принята с благодарностью. Я открыт для использования другой формулы, если есть лучшая, но не рекомендую переформатировать таблицу данных для предварительной сортировки значений.

Answer 1

Вы можете применить эту формулу массива:

{=PERCENTILE(IF((($A$2:$A$100=$F$3)*($B$2:$B$100=$G$3)),$C$2:$C$100),0.5)}

NB

1. В ячейках F3 и G3 вы должны хранить критерий, который делает Formula Dynamic, а не Hard Core.
2. Завершите формулу с помощью Ctrl+Shift+Enter .
3. При необходимости измените ссылки на ячейки в формуле.

Answer 2

Большое спасибо Rajesh S за помощь в моем вопросе! Вот формулы, которые я придумал, основываясь на его совете:

Процент вклада сотрудника - 25-й процентиль (замените 0,25 на 0,75 для расчета 75-го процентиля)

{= IFERROR(PERCENTILE.EXC(IF(((Взносы [Тип взноса] = "Тип 1")(Взносы [% соответствия взносов] <> 0)), Взносы [% соответствия взносов]), 0,25), «N /A ")} {= IFERROR(PERCENTILE.EXC(IF(((Взносы [Тип взноса] =" Тип 2 ")(Взносы [Соответствие взносов% <> 0)), Взносы [Соответствие взносов%]), 0,25), "N /A")} {= IFERROR(PERCENTILE.EXC(IF(((Взносы [Тип взноса] = "Тип 3")*(Взносы [% соответствия взносов] <> 0)), Взносы [% соответствия взносов ]), 0,25), "N /A")}

Процент вклада сотрудника - медиана (замените MEDIAN на AVERAGE для расчета средних значений)

{= IFERROR(MEDIAN(IF(((Вклады [Тип взноса] = "Тип 1")(Вклады [Соответствие взносов%] <> 0)), Взносы [Соответствие взносов%))), «Н / Д»)} {= IFERROR(MEDIAN(IF(((Вклады [Тип взноса] = "Тип 2")(Вклады [Соответствие взносов%] <> 0)), Взносы [Соответствие взносов%))), "Н / Д")} {= IFERROR(MEDIAN(IF(((Вклады [Тип вклада] = "Тип 3")*(Вклады [Соответствие вклада%] <> 0)), Вклады [Соответствие вклада%))), "Нет данных")}

Чтобы рассчитать Процент зарплаты - 25-е, Медиана, Среднее, 75-е - замените все случаи «Соответствия вклада%» в вышеприведенных формулах на «% Зарплаты»