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
----
excel_learner
(Using XL 2007 & 2010)
-------Learning never stops!
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.
----
excel_learner
(Using XL 2007 & 2010)
-------Learning never stops!
guys please assist
----
excel_learner
(Using XL 2007 & 2010)
-------Learning never stops!
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.
Last edited by excel_learner; 07-28-2013 at 03:02 PM.
----
excel_learner
(Using XL 2007 & 2010)
-------Learning never stops!
guys please assist
----
excel_learner
(Using XL 2007 & 2010)
-------Learning never stops!
It would be better if you clearly explain the idea, so that an appropriate logic can be built
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
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.
----
excel_learner
(Using XL 2007 & 2010)
-------Learning never stops!
What about this?
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Did you try post #5?
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Posting Permissions
Reply With Quote
Bookmarks