Calculating Average Book Cost

LoftlessOrganist

New Member
Joined
Jan 30, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I hope someone will be able to help on this. On the face of it, this is quite a simple task, but there’s one part I can’t work out.

Average cost price = Weighted average cost price of units purchased

Note that the formula ignores sales of units. See the attached simplified transaction file which shows a simplified transaction file and calculations on the right. I’ve shown the formulae I’ve used for Day 1 of the calcs. These formulae have been copied down.

Day 1
5,000 units bought for £1 each. Average cost = £1

Day 2
2,500 units bought for £3 each. Average cost = £1.67 (12,500/7,500)

Day 3
3,000 units sold for £3.50 each. Average cost remains £1.67

Day 4
4,500 units sold for £4 each. Average cost remains £1.67

Day 5
6,000 units bought for £5 each. Average cost should be £5 because we hold 5,000 units which cost £5 each.


You’ll see from the example that the all of the above works, until the trader has sold all units and buys new stock from scratch.

Any ideas to make this work please?
 

Attachments

  • Cost Example.JPG
    Cost Example.JPG
    60.7 KB · Views: 42

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
Does your file need to start again after 5 days? Why not have a column with dates and all the transactions running down the column next to it, then you can do an average between dates?
 

LoftlessOrganist

New Member
Joined
Jan 30, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
There's no logic as to how far back the average should look, only that it should only go as far back as the last time there were zero units held. The example I've provided is a very simple one. The actual file is almost 1,000 lines long and is increasing every day.
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
som,ething like this would work, where your running totals are in column H

=AVERAGEIF(H:H,"<>""",H:H)

it will only include cells that are not blank
 

Forum statistics

Threads
1,141,058
Messages
5,704,032
Members
421,323
Latest member
Exidous

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