I need a formula that will take the data from the purchase qty cell (some items have multiple purchases) and subtract that from the production qty. I want to subtract the production amount from the from oldest purchases first once that inventory is depleted I want it to subtract from the next purchase and so on for items that have multiple purchases, and I have formulas in the other cells which update when the data is changed I'm just not what formula to use to update the purchase qty colums.

Subcomponents	BOM Ratio	Qty Made Multiplied by BOM Ratio	Qty Made	Inventory Left After Production	Purchase Qty	Purchase Price	Cost Per Unit	Total Cost Per Finished Good
xyz	1	400	400	28,607
Purchase 1	1				7500	0.47	0.48338272	0.48
Purchase 2	1				8250	0.48
Purchase 3	1				10500	0.48
Purchase 4	1				9857	0.5