Help Calculating Average Cost using a Vlookup?

andrewsco

New Member
Hi,

I am hoping someone can help! I am trying to set up a spreadsheet for an online business that updates the 'cost of purchase' depending on which stock order has been sold.

For example, if I order 10 units of soap at $10 then the cost is $10. If I make a second order of 10 soaps at $12 then my overall average cost of soap has gone up to $11. What I am trying to show is that for each purchase made, I can look up and see (based on qty sold to date) whether the cost applicable to that particular soap bar is $10 or $12? Is this something that can be easily done?

I have attached a spreadsheet copy paste below that hopefully explains this a little more - the 'av cost' column on the right shows the answers I am looking for but I have just hard written the numbers in - ideally a formula to do this would be great!

CostRevenue
QtyUnit CostTotal CostQtyUnit RevTotal RevAv Cost
Soap 10 $ 10.00 $ 100.00 10Soap9 $ 20.00 $ 180.00 $ 10.00
Soap 10 $ 11.00 $ 110.00 Soap2 $ 20.00 $ 40.00 $ 10.50
Bags10 $ 15.00 $ 150.00 Soap9 $ 20.00 $ 180.00 $ 11.00
Bags10 $ 16.00 $ 160.00 Soap5 $ 20.00 $ 100.00 $ 12.00
Soap 10 $ 12.00 $ 120.00 20Soap3 $ 20.00 $ 60.00 $ 12.00
Bags10 $ 17.00 $ 170.00 30Soap2 $ 20.00 $ 40.00 $ 12.00

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

Thanks so much!
Andrew
 

Some videos you may like

This Week's Hot Topics

Top