I have a case where i have amounts to be distributed to different layers with different limits where min and max limits applies.
I have attached the worksheet as an example.
Kindly assist.
Thanks
I have a case where i have amounts to be distributed to different layers with different limits where min and max limits applies.
I have attached the worksheet as an example.
Kindly assist.
Thanks
Does this work for you?
=IF(SUM(C6:F6)<B6,SUM(C6:F6),B6)
no that is not what i want, the amounts in column B starting from B6 to B12 have to be distributed in C7 till F12 based on the limits specified above and maximum sum of limits upto 4th layer can be 185,000,000 as shown in B6 and distribution. Basically i want the distribution done as distributed in yellow highlighted area. The Column H6 to H12 shows total distribution to be done in C7 till F12 considering the max limit for distribution 185,000,000.
guys please assist
What about this?
Hi,
I have almost reached to the required formulas, however, some more help is needed. I have attached the sample worksheet highlighting (in green) some more fix to be done.
Please assist.
guys please assist
It would be better if you clearly explain the idea, so that an appropriate logic can be built
Basically, the amount has to be distributed in all layers according to the limit in row 3 "max limit of recovery". The limit starts from 15,000,000 and if the amount is less than this amount then no distribution otherwise first layer will get the difference. If the amount is 20,000,000 then first layer will get 20,000,000-15,000,000=5,000,000 and if it is 30,000,000 the first layer will get max 15,000,000 and the same applies to all layers as the balance of amount exceeding the previous layer max limit of recovery will go to next layer upto the respective layer limit and then so on. But sum of all layers, applying max limit, should not exceed 185,000,000.
I hope it is clear.
Did you try post #5?
sorry, somehow i did not see the post #5 it was just visible to me now. i cant figure out why i could not see it. however, i have tried it, and
i think i missed a point here recovery always will be amount-first layer deductible, means if the amount is 58,504,967 then total recovery will be 58,504,967-15,000,000=43,504,967 from 1st till 2nd layer (1st will get max 15,000,000 and second 43,504,967-15,000,000=28,504,967 and the rest of the layers will be 0.
In the file I had attached in post #5, use
C5=MIN(B5-C$3,C$3) and drag down
D5=MIN($B5-SUM($C$3,$C5:C5),D$3) and drag down and across
Thank you so much, it works great :)