How to allocate based on seniority

[Guest]

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

 

[Guest]

One try ..

Assume the 3 limits for obligations A to C
are entered in B22, 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 B33 and copy down as far as required to return
the required results.

 

[Max]

Sorry, pl dismiss earlier suggestion. Inadequately tested.

---

 

[Guest]

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.

 

[Guest]

Here's a revised go at this ..

Assume the 3 limits for obligations A to C
are entered in B22, 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(E3>SUM($C$2:$D$2),C$2,IF(AND(E3<=SUM($C$2:$D$2),E3/2<C$2),E3-D3,C$2)))

In D3:
=IF(E3="","",IF(E3>SUM($C$2:$D$2),D$2,IF(AND(E3<=SUM($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.

 

[Guest]

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]

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 !