# 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!

 Cost Revenue Qty Unit Cost Total Cost Qty Unit Rev Total Rev Av Cost Soap 10 \$ 10.00 \$ 100.00 10 Soap 9 \$ 20.00 \$ 180.00 \$ 10.00 Soap 10 \$ 11.00 \$ 110.00 Soap 2 \$ 20.00 \$ 40.00 \$ 10.50 Bags 10 \$ 15.00 \$ 150.00 Soap 9 \$ 20.00 \$ 180.00 \$ 11.00 Bags 10 \$ 16.00 \$ 160.00 Soap 5 \$ 20.00 \$ 100.00 \$ 12.00 Soap 10 \$ 12.00 \$ 120.00 20 Soap 3 \$ 20.00 \$ 60.00 \$ 12.00 Bags 10 \$ 17.00 \$ 170.00 30 Soap 2 \$ 20.00 \$ 40.00 \$ 12.00

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

Thanks so much!
Andrew

#### Sam_D_Ben

##### Active Member
How did you calculate the average cost.