Help Calculating Average Cost using a Vlookup?


New Member
Aug 26, 2019

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!

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>

Thanks so much!

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...