Conditional Prorata - formula

[andrei461]

Hello Friends,

Check the sheet attached. i want to distribute my inventory among different buyer (Col. B).

Each buyer has a restriction ( Col. D) upto which he can take.

I want to allocate (Col. E) my inventory on pro rata based on there nomination (Col. C)

Quest. - in E20 cell, i want a formula which will see, if during allocation a buyer met his restriction the left out amount to be distributed among other buyers(on pro rata basis).

Refer the attached sheet i hope it could explain my query more proficiently.

Thanks in advance.