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: 63

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top