Gurus - I am having trouble finding a formula that will allocate a number to
various buckets based on the seniority of these buckets. Let me exemplify:

I have operating cash flows of $200

Most senior bucket - 1 - Obligation A: $70
Second senior bucket - 2 - Obligation B: $180
Second senior bucket - 2 - Obiigation C: $60

So, the formula should allocate $200 by order of seniority -- allocating $70
to Obligation A since it is most senior. Then the remainder $130 (i.e $200 -
$70) should get allocated to second most senior. Since both Oblig B and C
are equally senior, remainder gets allocated to both equally or to the max of
the obligation. Which means that B will get $70 and C will get $60.

Any help is extremely appreciated. -- thanks

One try ..

Assume the 3 limits for obligations A to C
are entered in B2:D2, viz: 70, 180, 60

Source amounts are assumed input in A3 down, eg: 200

Place
in B3: =IF(A3="","",IF(A3<=B2,A3,B2))
in C3: =IF($A3="","",IF($A3<=$B$2,"",MIN(C$2,($A3-$B$2)/2)))
Copy C3 to D3. Then select B3:D3 and copy down as far as required to return
the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chunkey Pandey" wrote:
Gurus - I am having trouble finding a formula that will allocate a number to
various buckets based on the seniority of these buckets. Let me exemplify:

I have operating cash flows of $200

Most senior bucket - 1 - Obligation A: $70
Second senior bucket - 2 - Obligation B: $180
Second senior bucket - 2 - Obiigation C: $60

So, the formula should allocate $200 by order of seniority -- allocating $70
to Obligation A since it is most senior. Then the remainder $130 (i.e $200 -
$70) should get allocated to second most senior. Since both Oblig B and C
are equally senior, remainder gets allocated to both equally or to the max of
the obligation. Which means that B will get $70 and C will get $60.

Any help is extremely appreciated. -- thanks

Sorry, pl dismiss earlier suggestion. Inadequately tested.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Chunkey: This is just a though, I know you could use solver to reach your
goal as well. Tools--Solver
If you have never used this tool, you may want to get the help files warmed
up or one of the MVP's web sites should be able to walk you through it.


"Chunkey Pandey" wrote:

Gurus - I am having trouble finding a formula that will allocate a number to
various buckets based on the seniority of these buckets. Let me exemplify:

I have operating cash flows of $200

Most senior bucket - 1 - Obligation A: $70
Second senior bucket - 2 - Obligation B: $180
Second senior bucket - 2 - Obiigation C: $60

So, the formula should allocate $200 by order of seniority -- allocating $70
to Obligation A since it is most senior. Then the remainder $130 (i.e $200 -
$70) should get allocated to second most senior. Since both Oblig B and C
are equally senior, remainder gets allocated to both equally or to the max of
the obligation. Which means that B will get $70 and C will get $60.

Any help is extremely appreciated. -- thanks

Here's a revised go at this ..

Assume the 3 limits for obligations A to C
are entered in B2:D2, viz: 70, 180, 60

Source amounts are assumed input in A3 down, eg: 200

Place

In B3:
=IF(A3="","",IF(A3<=B$2,A3,B$2))

In C3:
=IF(E3="","",IF(E3SUM($C$2:$D$2),C$2,IF(AND(E3<=S UM($C$2:$D$2),E3/2<C$2),E3-D3,C$2)))

In D3:
=IF(E3="","",IF(E3SUM($C$2:$D$2),D$2,IF(AND(E3<=S UM($C$2:$D$2),E3/2=D$2),D$2,E3/2)))

In E3:
=IF($A3="","",IF($A3<=$B$2,"",$A3-$B$2))

Select B3:E3 and copy down as far as required. Hide away col E. Cols B to D
returns the required results. Col E can be collapsed into cols C and D, but
think it's simpler to leave it as-is.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


"Chunkey Pandey" wrote:

Gurus - I am having trouble finding a formula that will allocate a number to
various buckets based on the seniority of these buckets. Let me exemplify:

I have operating cash flows of $200

Most senior bucket - 1 - Obligation A: $70
Second senior bucket - 2 - Obligation B: $180
Second senior bucket - 2 - Obiigation C: $60

So, the formula should allocate $200 by order of seniority -- allocating $70
to Obligation A since it is most senior. Then the remainder $130 (i.e $200 -
$70) should get allocated to second most senior. Since both Oblig B and C
are equally senior, remainder gets allocated to both equally or to the max of
the obligation. Which means that B will get $70 and C will get $60.

Any help is extremely appreciated. -- thanks

Max -- Thank you. This helps, althoughI should have mentioned that the
seniority of obligation is not cell dependent - i.e. the most senior
obligation will not always occur in the first cell or any one particular
cell. Allobligations will be in entered and then user can assign 1 through
"n" against the obligation to define which is senior - 1 being the most
senior. The following may clarify as well (note B, C, and D are all equally
senior). Any help will be much appreciated! Thanks

Amount Available $200


Rank* Name Obligation Amt Paid Formula
1 A $70
2 B $40
2 C $55
3 D $30
2 E $30

* User input, random sequence



"Max" wrote:

Here's a revised go at this ..

Assume the 3 limits for obligations A to C
are entered in B2:D2, viz: 70, 180, 60

Source amounts are assumed input in A3 down, eg: 200

Place

In B3:
=IF(A3="","",IF(A3<=B$2,A3,B$2))

In C3:
=IF(E3="","",IF(E3SUM($C$2:$D$2),C$2,IF(AND(E3<=S UM($C$2:$D$2),E3/2<C$2),E3-D3,C$2)))

In D3:
=IF(E3="","",IF(E3SUM($C$2:$D$2),D$2,IF(AND(E3<=S UM($C$2:$D$2),E3/2=D$2),D$2,E3/2)))

In E3:
=IF($A3="","",IF($A3<=$B$2,"",$A3-$B$2))

Select B3:E3 and copy down as far as required. Hide away col E. Cols B to D
returns the required results. Col E can be collapsed into cols C and D, but
think it's simpler to leave it as-is.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


"Chunkey Pandey" wrote:

Gurus - I am having trouble finding a formula that will allocate a number to
various buckets based on the seniority of these buckets. Let me exemplify:

I have operating cash flows of $200

Most senior bucket - 1 - Obligation A: $70
Second senior bucket - 2 - Obligation B: $180
Second senior bucket - 2 - Obiigation C: $60

So, the formula should allocate $200 by order of seniority -- allocating $70
to Obligation A since it is most senior. Then the remainder $130 (i.e $200 -
$70) should get allocated to second most senior. Since both Oblig B and C
are equally senior, remainder gets allocated to both equally or to the max of
the obligation. Which means that B will get $70 and C will get $60.

Any help is extremely appreciated. -- thanks

Aha, that's a much clearer issue description. Sorry I'm out of further
suggestions to offer. Hang around awhile for possible responses from others.
Good luck !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chunkey Pandey" wrote in message
...
Max -- Thank you. This helps, although I should have mentioned that the
seniority of obligation is not cell dependent - i.e. the most senior
obligation will not always occur in the first cell or any one particular
cell. All obligations will be in entered and then user can assign 1
through
"n" against the obligation to define which is senior - 1 being the most
senior. The following may clarify as well (note B, C, and D are all
equally
senior). Any help will be much appreciated! Thanks

Amount Available $200


Rank* Name Obligation Amt Paid Formula
1 A $70
2 B $40
2 C $55
3 D $30
2 E $30

* User input, random sequence