Cumulative Inventory percentages by product - maintain Inv % by product until used up

We maintain inventory in three rotating piles. Each pile consists of 2 types of materials and all deliveries are stored in the first pile until full, then deliveries are stored in the next pile, etc..

Once the pile is full, the product mixture percentages must be reflected until the pile is fully consumed.

Consumption works the same way. Once material is taken from a pile, it continues to be consumed until the pile is empty and then the next pile is consumed.

I've set up my file based on daily totals.

The formula difficulties I'm having relate to combining the following factors:

1. keeping the % mix until pile empty
2. intermittent deliveries - some days there are no deliveries at all
3. Days when the pile is emptied in the morning, but due to low inventories, the pile may be used for storage later in the day.

See Sample Inventory file attached.

Any assistance is greatly appreciated. Thank you.