Proportional distribution upto the CAP

[andrei461]

Dear Team,

Kindly check the excel for detailed question along with the data. i have put up 2 example in the excel. the formula need to satisfy both in col. G

Inventory (Cell E5) is needed to be distributed among item (Col. B) proportionally. Every item has a requirement (Col. C) and there CAP(max. qty. allowed) in col. D.

Col. E shows normal proportional distribution. Whereas Col. F shows minimum of CAP qty and Proportional Qty.

Instead of col. F i want a formula in Col. G which will proportionally distribute upto the CAP and then distribute the remaining inventory to rest (upto the CAP).

I am not able to analyse how to form the formula in Col. G. I guess array formula will help in this.

Thanks in advance guys.