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...


--
Regards,
Luc.

"Festina Lente"


"PapaDos" wrote:

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...
--
Regards,
Luc.

"Festina Lente"


"Chunkey Pandey" wrote:

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.