Tabela de pesquisa do Excel e valores de correspondência com base na diferença de linha

Question

Eu tenho um painel diário que estou construindo onde preciso multiplicar uma entrada diária por um valor específico que muda quando a entrada diária ultrapassa um número de nível específico. O desafio que enfrento atualmente é que não consigo multiplicar o multiplicador de camada correto quando o valor do nível é atingido em um dia específico. Isso significa que o multiplicador de nível mais alto está sendo distribuído em todas as entradas diárias desse dia, em vez da diferença que acabou no nível superior nesse dia. Qualquer ajuda seria ótimo! Obrigado.

Fornecido é um exemplo prático:

1) Eu tenho uma tabela que tem os multiplicadores (Investimento) e o nível inicial e final.

2)Níveldeentradaatingidoem11/12.Comovocêveriaseadicionasseosnúmeros,11/11era464e11/12era506,oquesignificaque11das42entradasem11/12deveriamsermultiplicadasporUS$150emvezdeUS$200,oquenãoestáacontecendocomomeufórmulaatual

3)Multiplicandoosvaloresdeentradapelosvaloresdatabelacombasenapresençade"2º Nível" na tabela acima =IF(E6="",$V$3*$D6,$V$4*D6)

    

Answer 1

Aqui está a resposta revisada. A tabela abaixo mostra o investimento para 12 de novembro calculado corretamente.

AfórmulaemF2é:

=IF(SUM(D$2:E2)>475,IF(E1="",50*(SUM(D$2:D2)-475)+150*D2,200*D2),150*D2)

A maior parte é simples. Se a soma corrente da Coluna D (SUM (D $ 2: Dnn) for menor que 475, o Investimento será 150 * Dnn. Se a soma corrente for maior que 475 e a linha anterior da Coluna E não está em branco, então o investimento é de 200 * Dnn.

No caso em que a soma corrente é maior que 475 e a linha anterior da Coluna E é em branco (12 de novembro), então parte da entrada é multiplicada por 200 e o restante é multiplicado por 150 .

A porção da entrada a ser multiplicada por 200 é o excesso da soma corrente acima de 475 - esta é apenas a soma corrente menos 475. Então essa parte do Investimento é 200*(SUM(C$2:Cnn)-475) . O restante da quantia na Coluna D é Dnn menos essa quantia em excesso ou Dnn-(SUM(C$2:Cnn)-475) , e esta é a porção a ser multiplicada por 150. Representando a soma corrente como "Soma", o investimento total é:

200*(Sum - 475) + 150*(Dnn-(Sum-475))

Essa expressão pode ser usada na fórmula, mas pode ser simplificada. Simplificando dá:

200*Sum - 200*475 + 150*Dnn - 150*Sum + 150*475
50*Sum - 50*475 + 150*Dnn
50*(Sum-475) + 150*Dnn

e este é o "valor se verdadeiro" na instrução IF () interna:

50*(SUM(D$2:D2)-475)+150*D2

Espero que isso ajude e, por favor, desculpe o início falso no meu comentário acima.