Help Calculating Average Cost using a Vlookup?

[andrewsco]

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!

[TABLE="width: 704"]
<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 192, colspan: 3"]Cost[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 192, colspan: 3"]Revenue[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Qty[/TD]
[TD]Unit Cost[/TD]
[TD]Total Cost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Qty[/TD]
[TD]Unit Rev[/TD]
[TD]Total Rev[/TD]
[TD]Av Cost[/TD]
[/TR]
[TR]
[TD]Soap [/TD]
[TD="align: right"]10[/TD]
[TD="class: xl64"] $ 10.00 [/TD]
[TD="class: xl64"] $ 100.00 [/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]Soap[/TD]
[TD="align: right"]9[/TD]
[TD="class: xl64"] $ 20.00 [/TD]
[TD="class: xl64"] $ 180.00 [/TD]
[TD="class: xl65"] $ 10.00 [/TD]
[/TR]
[TR]
[TD]Soap [/TD]
[TD="align: right"]10[/TD]
[TD="class: xl64"] $ 11.00 [/TD]
[TD="class: xl64"] $ 110.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD]Soap[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl64"] $ 20.00 [/TD]
[TD="class: xl64"] $ 40.00 [/TD]
[TD="class: xl65"] $ 10.50 [/TD]
[/TR]
[TR]
[TD]Bags[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl64"] $ 15.00 [/TD]
[TD="class: xl64"] $ 150.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD]Soap[/TD]
[TD="align: right"]9[/TD]
[TD="class: xl64"] $ 20.00 [/TD]
[TD="class: xl64"] $ 180.00 [/TD]
[TD="class: xl65"] $ 11.00 [/TD]
[/TR]
[TR]
[TD]Bags[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl64"] $ 16.00 [/TD]
[TD="class: xl64"] $ 160.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD]Soap[/TD]
[TD="align: right"]5[/TD]
[TD="class: xl64"] $ 20.00 [/TD]
[TD="class: xl64"] $ 100.00 [/TD]
[TD="class: xl65"] $ 12.00 [/TD]
[/TR]
[TR]
[TD]Soap [/TD]
[TD="align: right"]10[/TD]
[TD="class: xl64"] $ 12.00 [/TD]
[TD="class: xl64"] $ 120.00 [/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]Soap[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl64"] $ 20.00 [/TD]
[TD="class: xl64"] $ 60.00 [/TD]
[TD="class: xl65"] $ 12.00 [/TD]
[/TR]
[TR]
[TD]Bags[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl64"] $ 17.00 [/TD]
[TD="class: xl64"] $ 170.00 [/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD]Soap[/TD]
[TD="align: right"]2[/TD]
[TD="class: xl64"] $ 20.00 [/TD]
[TD="class: xl64"] $ 40.00 [/TD]
[TD="class: xl65"] $ 12.00 [/TD]
[/TR]
</tbody>[/TABLE]

Thanks so much!
Andrew

 

[Sam_D_Ben]

How did you calculate the average cost.