Multiplicando diferentes fatores com base no valor de uma célula no Excel

Question

Estou tentando construir uma fórmula que calcule o seguinte:

=< 10,000 x 1.00

10,0001-12,500 x 1.25

12,501 - 15,000 x 1.50

Exemplos:

Se o número for 8.520 (8.520 x 1,00) = 8.520

Se o número fosse 11.560 (10.000 x 1,00) + (1.560 x 1,25) = 11.950

Se o número for 13.500 (10.000 x 1,00) + (2.500 x 1,25) + (1.000 x 1,50) = 14.625

Answer 1

Isso pode ser feito com um if. A maneira de construir um if aninhado é fazer o que acontece com valores diferentes. Supondo uma coluna de dados começando em A2, então

1. A2<10000. O resultado é a2 + 0 ou A2
2. 10000 <A2 <12501 O resultado é a2 +(0,25 * (A2-10000) )
3. 12500 < A2 <15000 O resultado é a2 +(0,25* (a2-10000)) + (0,25 *( A2-12500))

Para converter isso em uma única fórmula basta usar um if para cada caso. O fluxo é =if(test,true,false) onde se o teste for verdadeiro, obtenha o valor verdadeiro, caso contrário, obtenha o valor falso. A primeira solução usa 3 ifs, resultado de teste falso em zero, resultado verdadeiro na resposta

• =SE(A2<10001,A2,0)+SE(E(A2>10000,A2<12501),A2+(0,25*(A2-10000))+SE(E(A2>12500,A2<15001),A2+ (0,25*(A2-10000))+(0,25*(A2-12500))))

Isso pode ser simplificado usando um pouco de álgebra para

• =A2+SE(A2>10000,(0,25*(A2-10000)),0)+SE(A2>12500,(0,25*(A2-12500)))

Qualquer forma pode ser alterada de acordo com a forma como deseja lidar com números maiores que 15.000

Answer 2

Ok, foi um pouco mais complicado do que se pensava.

crie uma tabela como a seguinte:

Em seguida, use a seguinte fórmula de matriz:

=SUM(IF(A2>$D$2:$D$4,IF(A2<$E$2:$E$4,A2-$D$2:$D$4,$E$2:$E$4-$D$2:$D$4)*$F$2:$F$4,0))

Sendo uma fórmula de matriz, deve ser confirmada com Ctrl-Shift-Enter em vez de Enter ao sair do modo de edição. Se feito corretamente, o Excel definirá {}a fórmula.

---

Com base na fórmula do @fixer e tornando a fórmula dinâmica, criamos uma tabela como esta:

Como você pode ver, o Fator agora é o aumento do anterior e não o fator real.

Então podemos usar esta fórmula:

=A2+SUMPRODUCT((A2>$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*MOD(A2,$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*$E$3:INDEX(E:E,MATCH(1E+99,D:D)))

A fórmula agora é dinâmica, pois à medida que a tabela aumenta ou diminui, o mesmo acontece com a referência ao conjunto de dados. Encontra INDEX(D:D,MATCH(1E+99,D:D))a última célula na coluna e define-a como a extensão do conjunto de dados.