Excel Calcular Média enquanto a soma das Células ficam dentro de um determinado valor

[gikera]

Estou montando uma planilha para controlar o custo médio de alguns itens de meu estoque. Nessa planilha faço os lançamentos da quantidade de itens que Comprei e da quantidade de itens que Vendi, de forma que a quantidade em estoque é =Qtd.Compra-Qtd.Venda.

 

Como o preço de custo varia conforme a época eu não posso simplesmente fazer uma média para saber o custo médio do item, pois preciso desconsiderar os itens que já foram vendidos no cálculo.

 

Exemplificando:

 

. Col. A - Col.B - Col.C

1. Item - Custo) - Qtd.Compra

2. Prego - 1,00 - 200

3. Prego - 1,50 - 100

4. Martelo - 5,00 - 20

5. Prego - 1,20 - 150

 

Sabendo que ainda tenho em estoque a quantia de 200 Pregos (ou seja, que já vendi 250 Pregos, logo sobraram apenas 200), preciso calcular o custo médio desses 200 Pregos que me sobraram em estoque, sendo que preciso desconsiderar neste cálculo o custo dos Pregos que já foram vendidos).

 

Neste caso tenho que me sobraram 200 pregos: 150 custando 1,20 + 50 custando 1,50, logo daria um custo médio de 1,275 por prego.

 

Gostaria de montar uma fórmula/macro que eu entraria com o Nome do Item (Prego) e Quantidade em Estoque (200) e ela me retornasse o custo médio com base nas ultimas compras do item que somassem as 200 unidades.