# 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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Sam_D_Ben

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

Replies
1
Views
372
Replies
0
Views
179
Replies
1
Views
500
Replies
0
Views
41
Replies
14
Views
842

1,129,587
Messages
5,637,252
Members
416,963
Latest member
samfuge

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back