Hello,
I need help with a formula.
I have to come up with a formula which can assign one amount to different layers based on the criteria.
For example the amount is 4,350,0000 and first layer should have only up to 1 million, then second layer is next 500,000, 3rd layer next 500,000 and 4th layer 500,000, 5th layer 500,000, 6th layer 500,000 and 7th layer 850,000. I have attached a sheet.
Regards,
Jai
Try
Your amount in H11
H12 =IF(H$11>1000000,1000000,H$11)
H13 =IF(H$11> SUM(H$12:H12)+500000,500000,H$11-SUM(H$12:H12))
H14 =IF(H$11> SUM(H$12:H14)+500000,500000,H$11-SUM(H$12:H13))
H15 =IF(H$11> SUM(H$12:H15)+500000,500000,H$11-SUM(H$12:H14))
H16 =IF(H$11> SUM(H$12:H15)+500000,500000,H$11-SUM(H$12:H15))
H17 =IF(H$11> SUM(H$12:H16)+500000,500000,H$11-SUM(H$12:H16))
H18 =H$11-SUM(H$12:H17)
Martin
Changed the Amount column (C) to have numbers so they can be used in the formula. Rest is by custom format.
There are 2 formulas:
1 for the first layer calculating the smallest of the Total Amount and the layer amount and
Formula:
Please Login or Register to view this content.
1 for the other layers
Formula:Please Login or Register to view this content.
See attached workbook.
Cheers!
Tsjallie
--------
If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!
If you think design is an expensive waste of time, try doing without ...
Thanks a lot!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks