Inventory control system. Tracking average price.

Welcome to the forum.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

You are gpoing to have issues with this dreadful data layout, whatever happens - sorry.

As I said, you need to mock up what you want:

It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

In the absence of any mock-up, consider this: a normalised data layout is what you need. This means ONE ROW per TRANSACTION.

A transaction could be:

1. a sale;
2. stock acquisiton.

The key field would be the SKU, so this:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	C	D	E	F	G	H	I	J	K	L
9	11111	10	INV2021120113 22-Dec-2021	117.3	1172.7	5	2022-00004 28/05/2022	5	586.4
10		5	INV2022080018 3-Aug-2022	129.1	645.4	0		10	1290.8
11		6	INV2023100127 31-Oct-23	121.9	731.3	6	2023-00015 AL 25/8/23	10	1218.8	1,218.81

Sheet: End Dec 23

would need to become FIVE transactions (rows) that are DATED. Sales and stock purchases would be in one column, incomings and outgoings would each have a column. You could do the same for stock in and stock out, then you could have a financial balance column and a stock balance column.

So, have a go at changing the layout in this way.

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	N	O	P	Q	R	S	T	U	V	W	X
9	SKU	Date	Transaction (Sale/Stock Purchase)	Invoice	Unit Cost	Debit	Credit	Balance	Stock In	Stock Out	Stock Balance
10	1111	22/12/2021	Stock Purchase	INV2021120113		1172.7		1172.7	10		10
11	1111	28/05/2022	Sale	2022-00004			586.4	1759.1		5	5

Sheet: End Dec 23