Fórmula para preço Médio no Excel.

Question

Olá amigos,

Estou com dificuldade em fazer uma fórmula no Excel que resolva o seguinte problema:

Se eu colocar "2" no preço 5.416,00 e mais "2" no preço 5.413,00, o preço médio ficará em 5.414,50. 

       Compra          Preço           Venda

                           5.417,00

                           5.416,50

             2            5.416,00

                           5.415,50

                           5.415,00

          Médio       5.414,50

                           5.414,00

                           5.413,50

             2            5.413,00

                           5.412,50

                           5.412,00

                           5.411,50

                           5.411,00

                           5.410,50

                           5.410,00

Agora, se eu colocar mais "8" no preço 5.410,00, o preço médio ficará em 5.411,50.

       Compra          Preço           Venda

                           5.417,00

                           5.416,50

             2            5.416,00

                           5.415,50

                           5.415,00

                           5.414,50

                           5.414,00

                           5.413,50

             2            5.413,00

                           5.412,50

                           5.412,00

  Médio               5.411,50

                           5.411,00

                           5.410,50

             8            5.410,00

No exemplo, para o campo das compras, se compro no preço 5.416,00 e o preço cai, tenho que comprar uma quantidade maior mais abaixo para melhorar o preço médio. E se o preço vai caindo, tenho que comprar uma quantidade cada vez maior para que o preço médio venha mais para baixo. (conforme o exemplo na tabela).

Já no campo das vendas, o problema se dá de forma inversa: se vendo com um preço X e o preço continua subindo, tenho que vender uma quantidade cada vez maior num preço mais acima do preço inicial, de maneira a fazer o preço médio vir mais para cima... 

Os preços variam conforme o exposto na tabela, de 0,50 em 0,50 para cima ou para baixo, e na tabela que quero fazer o "range" dos preços vai dos 6.000,00 até os 2.000,00.

Os preços são sempre fixos, mas sempre que eu adicionar um número na coluna de compra ou de venda, o preço médio tem que ser recalculado para eu saber onde ele está. Então, eu posso comprar 2 a 5.416,00, depois mais 2 a 5.413,00, depois mais 8 a 5.410,00 (conforme exemplo) e continuar comprando mais 20 a 5.350,00, depois mais 65 a 5.299,00, e assim por diante...

***O resultado do preço médio da compra ou da venda (ou é compra ou é venda, nunca é compra e venda ao mesmo tempo) deve aparecer ao lado da tabela (em qualquer lugar, de preferência no topo) numa célula fixa, de modo que uma célula me mostre o preço médio da compra e na célula abaixo a quantidade total que foram adicionadas, e a outra célula me mostre o preço médio de venda, com sua respectiva quantidade total adicionada na célula logo abaixo.

Então, ao lado da tabela, ficaria algo assim, baseando-se pela tabela 2 do exemplo acima:

Preço médio de compra:  5.411,50

Quantidade de compra:        12

Preço médio de venda:    X

Quantidade de venda:     X

Espero que não tenha ficado muito confuso, e agradeço a quem puder ajudar!

Obrigado

Answer 1

Olá CNT LM,

Consegui resolver o problema!

Agradeço muito pela sua ajuda!

Fórmula da compra:  =SOMARPRODUTO(B2:B24;A2:A24)/SOMA(A2:A24)

Fórmula da venda:     =SOMARPRODUTO(C2:C24;B2:B24)/SOMA(C2:C24)!

https://learn-attachment.microsoft.com/api/attachments/ae185ca2-b88a-4c36-9ca7-24bd02545c7f?platform=QnA

Answer 2

Olá novamente CNT LM,

Sim, tem que somar os preços onde as compras são realizadas para determinar o preço médio em relação às quantidades totais, que nesse exemplo que fiz abaixo, são 102 quantidades compradas. (e 102 vendidas para melhor entendimento, mas note que a operação é inversa em relação às quantidades).

Não sei se teria alguma diferença na fórmula quando a operação for para venda, porque, na compra, eu venho comprando uma quantidade cada vez maior à medida que o preço caia. Já na venda, a operação se dá ao contrário, eu tenho que vender uma quantidade maior conforme o preço suba... e o resultado do preço médio de venda apareceria na célula F6. (O resultado do preço médio de compra apareceria na célula F2).

Note na barra da fórmula (fx) que você me passou, que eu só alterei de A3 para A24, e de B3 para B24, e posicionei o resultado na célula F, para ficar parecido com o que eu esperoo. No entanto, na célula F2, o resultado não aparece, mostrando apenas #NOME?.

Não sei porque o resultado ficou #NOME? na célula F2. Talvez eu tenha feito algo errado...

Obrigado novamente

Answer 3

Olá Marcos Rizzo

Esta foi a parte que eu não percebi de todo, porque o valor médio de 20 e 10 é 10 e não 15, a não ser que você esteja a querer dizer o valor médio da soma de 10+20 e nesse caso será 15.

Se for esta a questão aqui fica o exemplo:

A formula em B4 é =SOMA.SE(A2:A3;">0";B2:B3)/CONTAR.SE(A2:A3;">0")

Answer 4

Olá CNT LM

A questão é a seguinte:

Os preços são sempre fixos, e irão do valor 6.000,00 até 2.000,00 (de cima para baixo, começando com o maior valor no topo da tabela até o menor valor no fim da tabela, e sempre de 0,50 em 0,50).

O que preciso é que, conforme eu adicione um valor ao lado de determinado preço, ele me calcule o preço médio em relação ao valor que eu adicionar.

Então, se eu coloco uma quantidade 2 ao lado do preço 20, e depois eu coloco a quantidade 2 ao lado do preço 10, o preço médio será 15, porque as quantidades são equivalentes, e a média entre 20 e 10 é 15.

O problema é quando eu eu vou adicionando novas quantidades, porque, além dessas quantidades que já adicionei, posso continuar adicionando, por exemplo, mais uma quantidade de 6 ao lado do preço 08, e aí já seriam 3 quantidades adicionadas, e o preço médio teria que ser recalculado baseando-se nessa relação das 3 quantidades e 3 preços, ou seja, (2 a 20, 2 a 10 e 6 a 8).

Note que saber a média entre 2 preços é fácil, porque é justamente a metade, e isso com as quantidades iguais. Mas quando as quantidades são diferentes e você trabalha com vários preços, calcular a média fica difícil...

Na mesma linha, se eu compro 2 quantidades a 20 e depois eu compro 4 quantidades a 10, o preço médio não é mais 15, como citado logo acima, porque ao invés de eu adicionar 2 no preço 10, agora eu adicionei 4 no preço 10, e a média virá mais para baixo. Nesse caso, a média será 12,50.

Agora, imagine isso com várias quantidades compradas com os preços que vão de 6.000,00 até 2.000,00.

No fundo, é só um cálculo de preço médio entre várias quantidades diferentes em relação a vários preços diferentes.

Espero que tenha ficado mais claro...

Obrigado

Answer 5

Olá Marcos Rizzo

A explicação está um pouco complicada, você consegue dar um exemplo simples e prático do que você quer que aconteça?