usando uma fórmula and() com uma instrução if em uma fórmula de matriz no Excel

Question

OK, então tenho uma lista de devoluções de clientes em uma planilha. Cada cliente também possui uma categoria de risco atribuída ao ( riskyou safe) e ao parceiro atribuído a ele ( P1ou P2).

Aqui está um exemplo de layout

  A           B          C            D
Client  |  Return  |  RiskCat  |  Partner
_________________________________________
John       1.5%       risky       P2
Bill       1.8%       risky       P1
Tim        1.2%       safe        P2
Bob        1.4%       risky       P1
Kate       2.1%       risky       P2
Fred       0.8%       safe        P1
...etc

Eu escrevi uma medianif()função " " que calcula a mediana dos retornos em um determinado critério (por exemplo, a categoria de risco). Então, por exemplo, se eu quisesse encontrar a mediana dos retornos na categoria de risco, eu faria:

=median(if(C:C="risky",B:B,""))

E pressione ctrl-shift-enter para inseri-lo como uma fórmula de matriz na célula.

Da mesma forma para encontrar a mediana dos retornos para quem tem P1 como parceiro eu faria:

=median(if(D:D="P1",B:B,""))

Ambas as fórmulas acima funcionam para mim, mas digamos que eu queria encontrar a mediana de retornos para a categoria de risco E para o parceiro P1. Bem, eu tento:

=median(if(and(C:C="risky",D:D="P1"),B:B,""))

Infelizmente, isso não funciona e recebo um erro #value. É possível usar AND() dessa maneira e se não, como eu conseguiria o que busco?

Answer 1

Não, você não pode usar AND ou OR dessa forma porque essas funções retornam um único resultado em vez de um array. Você precisa usar * em vez de AND....ou vários IFs, por exemplo,

=MEDIAN(IF((C:C="risky")*(D:D="P1"),B:B))

ou

=MEDIAN(IF(C:C="risky",IF(D:D="P1",B:B)))

o último é possivelmente um pouco mais eficiente