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

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...