# Calculating Average Book Cost

#### LoftlessOrganist

##### New Member
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
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.

##### Well-known Member
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
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.

##### Well-known Member
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

Replies
0
Views
262
Replies
3
Views
91
Replies
2
Views
242
Replies
0
Views
174
Replies
2
Views
116

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.

### Which adblocker are you using?

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

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