Questão Se AND OU Função aninhada com múltiplas respostas


Estou tendo problemas com uma fórmula IF / AND / OR aninhada bastante complexa para gerenciar um incentivo de vendas em que há uma porta de pagamento e vários limites de pagamento.

Regras são:

  • Salesrep ganha 1 $ por cada produto vendido E se a sua "taxa" é superior a 30%.
  • O pagamento é limitado a 250 $ se a taxa estiver entre 30-35%
  • O pagamento é limitado a 350 $ se a taxa estiver entre 35-40%
  • O pagamento é limitado a 500 $ se a taxa estiver entre 40-45%,
  • O pagamento é limitado a 750 $ se a taxa for superior a 45%

A figura mostra um exemplo básico de uma planilha do excel para lhe dar uma ideia mais clara do que estou tentando fazer. Eu simplesmente não consigo descobrir a fórmula para preencher as colunas E2: E5 em amarelo.

Excel image

Qualquer ajuda seria mais que útil. Eu tentei separar as fórmulas em várias colunas extras, mas não consigo chegar ao resultado final correto. Muito Obrigado.


2


origem


Por que isso está recebendo downvotes? A questão é clara, um exemplo foi dado e a saída esperada é clara o suficiente. Por favor, seja gentil, indique a razão pela qual esta pergunta é inútil, se você votar dessa maneira. - Ramhound


Respostas:


Você tem que colocar isso no celular E2:

=IF(C2>0.3,IF(C2<=0.35,250,IF(C2<=0.4,350,IF(C2<=0.45,500,IF(C2>0.45,750,0)))),0)

Uma explicação:

If C2>0.3 then
    If C2<=0.35 then
          250
    Else
          If C2<=0.4 then
               350
          Else
               If C2 <=0.45 then
                     500
               Else
                     If C2>0.45 then
                          750
                     Else
                           0
                     End If
               End If
          End If
    End If
Else
    0
End If

Na célula F2 colocar: =IF(D2>E2,E2,D2) e esse é o resultado final.


2



=IF(D2>E2,E2,D2) pode ser expresso de forma mais concisa =MIN(D2,E2). - Scott
Isso é o que eu estava tentando fazer e está mais de acordo com minha própria lógica (e competência) do que as outras duas respostas. Meu problema era que eu estava tentando tornar mais complicado com AND / OR. - james


Você pode fazer isso sem muito aninhamento complicado usando MIN, INDEXe MATCH. Coloque o seguinte em E2 e preencha.

=MIN(D2,INDEX({0,250,350,500,750},MATCH(C2,{0,0.3,0.35,0.4,0.45},1)))

Como isso funciona:

MATCH(C2,{0,0.3,0.35,0.4,0.45},1) compara a porcentagem em C2 para a matriz de limites inferiores para os limites de limite de pagamento. o MATCH função retornará onde no array C2 cai. Por exemplo, se C2 for 10%, a função descobrirá que 10% está entre 0% e 30%, então retornará 1 porque cai no primeiro intervalo. Se C2 fosse 33%, retornaria 2 porque 33% cai na segunda faixa, entre 30% e 35%.

O valor retornado pelo MATCH função é então usada pelo INDEX função para retornar um valor da mesma posição na matriz de limites de pagamento. Então, por exemplo, se MATCH retorna 1, então INDEX retornará o primeiro número da matriz, 0. E se MATCH retorna 4, INDEX retornará o quarto número da matriz, 500. Juntos, MATCH e INDEX funciona como uma tabela de pesquisa.

O último passo é o MIN função, que compara o valor ilimitado em D2 para a tampa retornada pelo INDEX função. Ele retorna o menor dos dois valores, exatamente como a regra de limite determina.


2



Você postou isso enquanto eu estava trabalhando na minha resposta e eu notei isso. Precisa ser inserido como uma fórmula de matriz? - fixer1234
Sim, vejo que tivemos a mesma ideia, mas abordagens diferentes para a pesquisa. Isso realmente funciona sem entrada de matriz. MATCH e INDEX tomará arrays como argumentos sem entrada especial. - Excellll
Obrigado Excellll. Funciona perfeitamente. Nunca usei a correspondência ou as funções de índice antes. Preciso gastar um pouco mais de tempo para entender como usá-los melhor, pois frequentemente estou criando regras de incentivo estúpidas para meus representantes de vendas, e eles podem ser úteis novamente. - james


Como a maioria das coisas no Excel, há mais de uma maneira de resolver um problema. Você perguntou especificamente sobre como resolvê-lo com a lógica IF, e a resposta de jcbermu faz isso. Outro método é fazer isso com uma tabela de pesquisa:

![![enter image description here

Ele fornece uma fórmula mais simples para esse tipo de problema. A fórmula em E2, que você pode copiar a coluna conforme necessário, é:

=MIN(B2,VLOOKUP(C2,G$2:H$6,2))

Em vez de definir min e max de cada intervalo, você veria como a taxa se compara ao mínimo de cada intervalo. Taxas abaixo do mínimo de qualificação de 30% têm um limite de $ 0.

O VLOOKUP encontra a maior taxa na tabela que não excede o valor na coluna C e retorna o limite associado. A função MIN retorna o menor valor do cálculo de pagamento bruto (desde que 1vezes o valor da coluna B, estou usando apenas o valor da coluna B) ou o limite.


2



Uau, tão simples .. nunca pensaria em usar vlookup dentro da mesma folha. O cálculo real que preciso fazer é mais complexo, mas sua fórmula funciona bem. Graças a um milhão .. - james