How to add up inventory in store

Hello guys,

I currently want to develop a inventory stock summary for my own shop however I am stuck in this process

I had products which has small box packaging inside a big large box (for example 1 big box of product A contain of 10 small box of product A)

In opening stock, I do have 10 big box of product A

Customer A take 5 small box

Incoming stocks 1 big box plus 8 small box

The remaining stocks should be 11 big box plus 3 small box

I would like the excel to automatically to calculate the balance of the stocks (automatically add to big box once small box exceeds 10 units)



Hope you all can clarify me

attached is a table that does what you are asking. It uses a helper column which could be hidden if desired. There would also be the ability to add a macro which would start the process over again once the transactions (Incoming/Outgoing) are completed.

Store Inventory.xlsx

Hope this helps.

Appreciated your help but I prefer not to use helper column since I had 50++ different product and I personally not prefer hiding the helper column

Or there is no available function that could automatically to add up big box column once small box column exceed 10 units

Thanks in advance