Este fórum está sendo desativado

[Gustavo]

Prezados boa tarde, estou com uma dificuldade e gostaria da ajuda de vcs,
Tenho uma formula em uma de minhas planilhas que me mostra o lucro sobre um trabalho em porcentagem (plan de orçamento).
Por exemplo, o valor do orçamento de 10,00 ele me mostra o quanto lucramos sobre este valor.
Porém tenho dentro deste valor a comissão de vendedor, e gostaria que me calculasse a comissão com base na minha margem de lucro,
Ex:
se meu lucro estiver maior que 29% ele me retorna 10%, se tiver entre 28,9% e 27% me retorna 9%, se tiver entre 26,9% 25% me retorna 8% e assim por diante mas a formula que fiz ficou assim
=SE(B86>=29%;10%;SE(B86<=28,9%>27%;8%;SE(B86<=26,9%>25%;7%;SE(B86<=24,9%>23%;6%;SE(B86<=22,9%>21%;5%;SE(B86<=20,9%>19%;4%;3%))))))
Porém ele vai me calcular sobre estes valores que acontece isso quando aplico um desconto, sendo assim a porcentagem da margem reduz pois dei o desconto, ai queria que a comissão do vendedor fosse proporcional, alguém sabe como resolver ?
Obrigado

[laennder]

Gustavo, anexe a sua planilha por favor.

[Gustavo]

Segue a planilha em anexo, para que consiga ter uma noção do que preciso preencha a planilha materiais diversos as celulas e9 com a qtde e a f9 com o valor, vera que a planilha orçamento irá lhe apresentar o valor do orçamento na celula b85,
O desconto se aplica na celul c84, mas para o calculo da formula que postei acima a base é a b86 onde a mesma fica travada para que não se mexa na margem de contribuição então queria que o retorno do resultado fosse na celula b36 onde hj coloco manualmente a comissão do vendedor. Veja se fui claro, estou tentando explicar pois tem 8 planilhas ali nesta pasta de trabalho onde é gerado meu orçamento.
Obrigado

[DJunqueira]

Gustavo, sua planilha está repleta de referências circulares e isso é um grave erro!!
Procure corrigir esses problemas antes de avançar com mais cálculos.

[Gustavo]

Sim ela tem referencia circular pois preciso que os valores sejam sempre baseados no calculo da margem de contribuição, minha margem fica baseada na porcentagem, então se eu tenho uma comissão minha margem continua a mesma, não sei como resolver de outra forma Djunqueira

[DJunqueira]

Referência circular não é uma opção, é ERRO
Trava a planilha e não faz sentido.
Quando A1 tem referência em B1 q por sua vez é uma porcentagem de A1 isso gera um looping infinito e trava a planilha, vc 'gostariiiia...' q o Excel só calculasse uma vez, mas não é assim q funciona.
Vc tem q rever esse erro p/ sua planilha ficar funcional.

[Gustavo]

Djunqueira queria entender pq a referencia circular é um erro ?

[laennder]

Não necessariamente referência circular é um erro. Não vi a sua planilha pra saber se é o caso.

Leia mais sobre referências circulares:

http://chandoo.org/wp/2010/09/16/excel- ... eferences/

[Gustavo]

Laennder eu utilizo a referencia circular pois dependo do resultado, eu até comecei a fazer outra planilha de orçamento mas caio na mesma situação, pois ela funciona da seguinte maneira, os valores dos impostos não podem comer meu lucro por exemplo, então ela refaz o calculo, se tiver um tempo de uma olhada, e se tiver outro jeito de fazer que não soube fazer agradeceria se vcs me auxiliassem nestas lógicas.

[DJunqueira]

Não necessariamente referência circular é um erro. Não vi a sua planilha pra saber se é o caso.
Leia mais sobre referências circulares:
http://chandoo.org/wp/2010/09/16/excel- ... eferences/

Vc tem razão laennder, isso é possível, mas para quem nem sabe montar uma fórmula com vários SE() o melhor seria evitar.
Apesar de ter o conhecimento teórico desta possibilidade eu nunca a utilizei e nas raríssimas vezes q li s/ o assunto outras alternativas me pareceram ser mais práticas.
Seja como for agradeço por mais este artigo.

Fórmulas p/ a questão inicial:

Código: Selecionar todos

=SES(B86<0,19;0,03;B86<0,21;0,04;B86<0,23;0,05;B86<0,25;0,06;B86<0,27;0,07;B86<0,29;0,08;B86>=0,29;0,1)

ou

Código: Selecionar todos

=SE(B86<0,19;0,03;SE(B86<0,21;0,04;SE(B86<0,23;0,05;SE(B86<0,25;0,06;SE(B86<0,27;0,07;SE(B86<0,29;0,08;0,1))))))

ou para um incremento linear

Código: Selecionar todos

=SE(B86<0,19;0,03;SE(B86>0,29;0,1;(B86-0,19)/1,42857142857143+0,03))

[Gustavo]

Djunqueira eu não tenho com evitar, pois alguns resultados são dependentes e assim se faz necessário utilizar a ref circular, quanto ao looping vc tem como colocar para ele utilizar apenas de um cálculo, mas o que me surpreendeu é que vc me diz que esta errado, mas não utiliza o recurso, a minha duvida não é utilizar o Se, mas como tenho uma resultante dependente de outra resultante queria saber de que forma faria isso. Sem mais.
Sem mais

[DJunqueira]

Gustavo, no endereço q o Laennder nos brindou tem uma parte s/ 'How to avoid Circular References?' (como evitar referências circulares), esta seria certamente a minha solução p/ vc e é o q faço no meu dia a dia. A sua planilha é complexa, tem vários cálculos, habilitar a referência circular pode ter consequências imprevisíveis nesse cenário q podem levar ao erro.

Mas se vc prefere assim, é a sua planilha, vc quem decide. Apenas um exemplo rápido de como se livrar de uma referência circular, suponha q sua receita é 100, seu lucro sem desconto de comissão é 20 e a comissão s/ esse lucro é de 5% numa referência circular. No final das contas o q o vendedor vai receber (arredondado) é 0,95% da receita e o lucro final será 19,05% e isso será p/ qq valor. Portanto p/ se livrar da referência circular basta usar a nova percentagem como vc pode constatar na planilha anexa.

Outro detalhe q verifiquei agora montando uma planilha exemplo é q o resultado não é estável com o default de 100 iterações, ao alterar a receita no exemplo e depois alterando o valor de qq célula (no caso H12 q tem um incrementador) o valor final se altera! Verifique por vc mesmo.
Isso provavelmente se deve ao número de iterações, mas demonstra tb o quanto não confiável é esse tipo de uso do Excel.

Quanto a sua dúvida inicial te forneci 3 opções de solução.

[Gustavo]

Dunqueira, não é so ref a comissão mas tem impostos e outras coisas ao qual ele manter a Margem de contribuição, fica mais pratico usar a ref circular.