Please-please-HELP - Really need this resolved - Allocation formul

[Guest]

I have a very pressing need to find a solution to this problem below. Any
help towards this is very much appreciated.

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.

I should also mention 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 for distribution $200

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

* User input, random sequence, not dependent on cell – i.e. most senior
obligation can occur at the very end of table, for example.

 

[Guest]

Please give accurate data and explanations.
What you say doesn't correspond to the data given.

When exactly do you "split" the money between "equally seniored" individuals
and do you favor one of them ?

 

[Guest]

Chunkey,

what is your condition,

let's say the remaining budget for senior 2 is $51

Rank* Obligation Amt Paid OR Amt Paid
1 $149 $149 $149
2 $50 $50 51*50/(50+100)
2 $100 $1 51*100/(50+100)

thiis is a solver problem..

 

[Guest]

Still not sure about the details, but assuming your table is in A1 and the
Amount to distribute is in H1, this ARRAY FORMULA in E2 may work:

=MIN( C2, MAX( 0, $H$1 - SUMIF( $A$2:$A$6, "<" & A2, $C$2:$C$6 ) - SUM(
($A$2:$A$6 = A2) * ($B$2:$B$6 <> B2) * IF( $C$2:$C$6 < ( $H$1 - SUMIF(
$A$2:$A$6, "<" & A2, $C$2:$C$6 ) ) / COUNTIF( $A$2:$A$6, A2 ), $C$2:$C$6, (
$H$1 - SUMIF( $A$2:$A$6, "<" & A2, $C$2:$C$6 ) ) / COUNTIF( $A$2:$A$6, A2 ) )
) ) )

Drag/Fill as needed.

If I got it wrong, we need more details...

 

[Guest]

My previous formula has flaws, here is a slightly better one:

=MIN( C2, MAX( 0, ( $H$1 - SUMIF( $A$2:$A$12, "<" & A2, $C$2:$C$12 ) -
SUMPRODUCT( ($A$2:$A$12 = A2) * ($B$2:$B$12 <> B2) * ($C$2:$C$12) * (
$C$2:$C$12 < ( ( $H$1 - SUMIF( $A$2:$A$12, "<" & A2, $C$2:$C$12 ) ) /
COUNTIF( $A$2:$A$12, A2 ) ) ) ) ) / MAX( 1, SUMPRODUCT( ($A$2:$A$12 = A2) * (
$C$2:$C$12 >= ( ( $H$1 - SUMIF( $A$2:$A$12, "<" & A2, $C$2:$C$12 ) ) /
COUNTIF( $A$2:$A$12, A2 ) ) ) ) ) ) )

But it won't split the full amount in all situations.
I don't think it can be done with straight formulas...