Como eu uso uma matriz condicional com mais de uma condição para calcular os percentis?

Question

Eu tenho uma tabela no excel com os seguintes dados:

+-------------------+----------------------+----------+
| 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 |
+-------------------+----------------------+----------+

Eu gostaria de usar uma fórmula de matriz para calcular quartis para cada tipo de contribuição com base na condição adicional (porque o tipo de contribuição é a primeira condição) que os valores de dados relevantes não são deixados em branco ou inseridos como um valor zero.

O tipo 1 corresponde a 100% do salário até um determinado percentual (X) da contribuição dos funcionários:

{=PERCENTILE.EXC(IF(Contributions[Contribution Type]="Type 1",Contributions[% Contribution Match]),0.25)} (etc.for med, avg, and 75th)

O tipo 2 corresponde a um percentual limitado (Y) de salário, sem limite para a contribuição do funcionário:

{=PERCENTILE.EXC(IF(Contributions[Contribution Type]="Type 2",Contributions[% Salary]),0.25)}(etc.for med, avg, and 75th)

O tipo 3 tem limites tanto na contribuição do funcionário quanto nas porcentagens salariais:

{=PERCENTILE.EXC(IF(Contributions[Contribution Type]="Type 3",Contributions[% Contribution Match]),0.25)} (etc.for med, avg, and 75th)

{=PERCENTILE.EXC(IF(Contributions[Contribution Type]="Type 3",Contributions[% Salary]),0.25)} (etc.for med, avg, and 75th)

A tabela resultante calcula quartis incluindo valores em branco e zero (não o que eu quero):

+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+
|         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%      |
+--------+-----------+--------+---------+------------+-----------+--------+---------+------------+

Eu tentei todas as combinações de IF (AND IF (SE eu posso pensar). Sou relativamente novo no uso de fórmulas de array, então qualquer ajuda seria muito apreciada. Estou aberto a usar uma fórmula diferente se houver uma melhor um, mas desencorajado de reformatar a tabela de dados para pré-valores.

    

Answer 1

Você pode aplicar essa fórmula de matriz:

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

N.B.

1. Na célula F3 & G3 você tem que armazenar Criterion, que faz da Formula Dynamic que o núcleo duro.
2. Conclua a fórmula com Ctrl+Shift+Enter .
3. Ajuste referências de célula na fórmula conforme necessário.

Answer 2

Muito obrigado a Rajesh S por sua ajuda com a minha pergunta! Aqui estão as fórmulas que eu criei, com base em seu conselho:

Porcentagem de contribuição de funcionários - 25º percentil (substitua 0,25 por 0,75 para calcular o 75º percentil)

{=IFERROR(PERCENTILE.EXC(IF(((Contributions[Contribution Type]="Type 1")(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match]),0.25),"N/A")} {=IFERROR(PERCENTILE.EXC(IF(((Contributions[Contribution Type]="Type 2")(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match]),0.25),"N/A")} {=IFERROR(PERCENTILE.EXC(IF(((Contributions[Contribution Type]="Type 3")*(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match]),0.25),"N/A")}

Porcentagem de Contribuição de Empregado - Mediana (substitua MEDIANO por MÉDIA para calcular valores médios)

{=IFERROR(MEDIAN(IF(((Contributions[Contribution Type]="Type 1")(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match])),"N/A")} {=IFERROR(MEDIAN(IF(((Contributions[Contribution Type]="Type 2")(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match])),"N/A")} {=IFERROR(MEDIAN(IF(((Contributions[Contribution Type]="Type 3")*(Contributions[% Contribution Match]<>0)),Contributions[% Contribution Match])),"N/A")}

Para calcular a porcentagem de salário - 25ª, mediana, média, 75ª - substitua todas as instâncias de "% de correspondência de contribuição" nas fórmulas acima por "% do salário"