Multiplicando fator diferente com base no valor de uma célula no Excel

Question

Estou tentando criar 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 aninhado é fazer o que acontece em diferentes valores. 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 + (. 25 * (A2-10000))
3. 12500 < A2 < 15000 O resultado é a2 + (. 25 * (a2-10000)) + (.25 * (A2-12500))

Para converter isso em uma única fórmula, use um if para cada caso. O fluxo é = if (teste, verdadeiro, falso) onde se o teste for verdadeiro, obtenha valor verdadeiro, senão obtenha valor falso. A primeira solução usa 3 ifs, resultado do teste falso em um zero, resultados verdadeiros na resposta

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

Isso pode ser simplificado usando alguma álgebra para

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

Qualquer forma pode ser alterada sobre como você 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:

Emseguida,useaseguintefórmuladematriz:

=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))

Sendoumafórmuladematriz,eladeveserconfirmadacomCtrl-Shift-EnteremvezdeEnteraosairdomododeedição.Sefeitocorretamente,oExcelcolocará{}emtornodafórmula.

Criandoafórmulado@fixeretornandoafórmuladinâmica,criamosumatabelacomoesta:

Comovocêpodever,ofatoragoraéoaumentodofatoranteriorenãooreal.

Entãopodemosusaressafó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)))

AFórmulaagoraédinâmicanamedidaemqueatabelaaumentaoudiminui,assimareferênciaaoconjuntodedados.OINDEX(D:D,MATCH(1E+99,D:D))encontraaúltimacélulanacolunaedefineissocomoaextensãodoconjuntodedados.