Se E / OU Função aninhada com múltiplas respostas

Question

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.

As regras são:

• Salesrep ganha 1 $ por cada produto vendido se a sua "taxa" for 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 folha de 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.

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.

    

Answer 1

Você precisa colocar isso na célula 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 put: =IF(D2>E2,E2,D2) e esse é o resultado final.

    

Answer 2

Você pode fazer isso sem muito aninhamento complicado usando MIN , INDEX e 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 com a matriz de limites inferiores para os intervalos de limite de pagamento. A função MATCH retornará onde, no array C2 , cai. Por exemplo, se C2 for 10%, a função descobrirá que 10% está entre 0% e 30%, portanto, 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 pela função MATCH é usado pela função INDEX para retornar um valor da mesma posição na matriz de limites de pagamento. Portanto, por exemplo, se MATCH retornar 1 , então INDEX retornará o primeiro número da matriz, 0 . Se MATCH retornar 4 , INDEX retornará o quarto número da matriz, 500 . Juntos, MATCH e INDEX funcionam como uma tabela de pesquisa.

A última etapa é a função MIN , que compara o valor não limitado em D2 com o limite retornado pela função INDEX . Ele retorna o menor dos dois valores, exatamente como a regra de limite determina.

    

Answer 3

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:

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 da qualificação mínima de 30% têm um limite de $ 0.

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