Help Calculating Average Cost using a Vlookup?

andrewsco

New Member
Joined
Aug 26, 2019
Messages
1
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
 

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

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top