You don't say how you store your data.

If your quantity is in column A and the unit cost in column B, e.g.:

A B
1 10000 1.08
2 5000 0.90

then your average cost is

=SUMPRODUCT(A1:A1000,B1:B1000)/SUM(A1:A1000)

which will give you $1.02.

Note, however, that this assumes that the entire quantity is still in
inventory. If you make deductions, you may want something like:


A B C
1 Additions Unit Cost Sales
2 10000 1.08 5989
3 5000 0.90 0

Using

=SUMPRODUCT((A1:A1000-C1:C1000),B1:B1000)/SUM(A1:A1000,-C1:C1000)

would return an average unit cost of 0.98



In article ,
Inventory Question <Inventory
wrote:

I want to calculate a new average price on inventory each time I add new
product. For example if I buy 10,000 widgets at $1.08 per widget and then I
buy another 5,000 widgets at .90, what is my current average price per widget?