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

у меня есть таблица в 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) от вклада сотрудников:

{=процентиля.EXC (если (вклады[тип вклада]= " тип
1",взносы[% вклад матч]),0.25)} (и т. д.для med, avg, и
75-е)

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

{=процентиля.EXC (если (вклады[тип вклада]= " тип
2", взносы [%зарплаты]), 0.25)} (и др.для med, avg и 75-го)

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

{=процентиля.EXC (если (вклады[тип вклада]= " тип
3"взносы[% вклад матч]),0.25)} (и т. д.для med, avg, и
75-е)

{=процентиля.EXC (если (вклады[тип вклада]= " тип
3", взносы [%зарплаты]), 0.25)} (и др.для med, avg и 75-го)

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

+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+
|         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(и IF (если я могу думать. Я относительно новичок в использовании формул массива, поэтому любая помощь будет принята с благодарностью. Я открыт для использования другой формулы, если есть лучшая, но не рекомендуется переформатировать таблицу данных для предустановки значений.

20
задан SuziLmrdo
12.05.2023 10:36 Количество просмотров материала 2791
Распечатать страницу

2 ответа

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

{=PERCENTILE(IF((($A:$A0=$F)*($B:$B0=$G)),$C:$C0),0.5)}

Б. Н.

  1. В Ячейке F3 & G3 вы должны хранить Критерий, который делает Формулу динамической чем твердое ядро.
  2. закончить формулу с Ctrl+Shift+Enter.
  3. изменить ссылки на ячейки в Формуле по мере необходимости.
0
отвечен Rajesh S 2023-05-13 18:24

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

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

{=IFERROR (ПРОЦЕНТИЛЬ.EXC (если (((вклады[тип вклада]= "Тип 1")(Вклады [%Вклад Матч]<>0)), Вклады [%Вклад Матч]), 0.25), " Н / Д")} {=IFERROR (ПРОЦЕНТИЛЬ.EXC (если (((вклады[тип вклада]= "Тип 2")(Вклады [%Вклад Матч]<>0)), Вклады [%Вклад Матч]), 0.25), " Н / Д")} {=IFERROR (ПРОЦЕНТИЛЬ.Предоставление услуг по монтажу, (если(((вклад[вклад тип]="тип 3")*(взносов[% вклад матч]<>0)),взносов[% вклад матч]),0.25),"Н/А")}

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

{=IFERROR (медиана (если (((вклады[тип вклада]= "Тип 1")(Взносов[% Вклад Матч]<>0)),Взносов[% Вклад Матч])),"Н/А")} {=IFERROR (медиана (если (((вклады[тип вклада]= "Тип 2")(Взносов[% Вклад Матч]<>0)),Взносов[% Вклад Матч])),"Н/А")} {=Функция iferror(медиана(если(((взносов[вид вклада]="Тип 3")*(взносов[% вклад матч]<>0)),взносов[% вклад Match])),"N/A")}

для расчета процента заработной платы-25-й, средний, средний, 75-й-заменить все случаи "% матч вклад" в приведенных выше формулах с "% от заработной платы"

0
отвечен SuziLmrdo 2023-05-13 20:41

Постоянная ссылка на данную страницу: [ Скопировать ссылку | Сгенерировать QR-код ]

Ваш ответ

Опубликуйте как Гость или авторизуйтесь

Имя
Вверх