Proportional distribution upto the CAP

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.

Originally Posted by bebo021999

Here is how I built formula:
Total delta availble:
SUM($H$8:$H$11)

Sum of Required of items need to be distributed:
SUMIF($H$8:$H$11,0,$C$8:$C$11)

Qty of Item in C8 will be distributed:
C8/SUMIF($H$8:$H$11,0,$C$8:$C$11)*SUM($H$8:$H$11)

Final formula in G8:

Please Login or Register  to view this content.

Drag down

Thanks bebo for your response . Ur formula does help in distributing the delta. But can we derive a simple formula which wont use the helper column(Col. H).

Check the attached sheet.

U only got total inventory. And every items requirement and there respective CAP.

Now in col E, inventory is distributed to every item upto there cap Once the cap is met the remaining inventory is then distributed among those whose cap has not met yet.

How we can formulise above mentioned scenario in column E that it will give the desired result (as entered in col. E).

I saw ur formula and OMG thats one hell of a formula, i am trying to understand it and follow it.

Just one more thing i want to ask ur help with, say i entered a category col. in the exel. Now my distribution philosophy is same but it will now have to work category wise. i.e., in col. F category 1 item will be distributed upto there minimum of cap or nom. If after that inventory remains it will distribute the remaining inventory in col. G for category 2 item and so on.

I want your formula to include category also for distribution.

thanks for Your help.

Attached excel model to help visualise the problem.

Thanks @kvsrinivasamurthy this is the type of formula that i was looking about.
What u doing is that taking the minimum value of requirement and cap and distributing the inventory. However my requirement is as below:

I want a formula in F21 which do the three steps i.e.,
Step 1 - distribute the inventory on the basis of there requirement (as illustrated in col. J),
Step 2- Limit the distribution upto there Max. cap (as illustrated in Col. K) and
Step 3, distribute the delta to remaining item (as illustrated in Col. M)

i know its a bit complicated but m sure array formula can help it. I just dont have any idea how to use that.

Check the attached excel.

Originally Posted by kvsrinivasamurthy

ARRAY formula in F21 then copied across.

Please Login or Register  to view this content.

this worked like a charm. Thanks @kvsrinivasamurthy.