Allocation
 

Hi,

Looking for unique formula to allocate the past due amounts for customer
deliquency.

Col A = Monthly Payment
Col B = Total payments due
Col C = 30 days past due, Col D = 60 days past due etc. and goes up to Col H.

Col c thru h is not given. what is the unique formula for Col C thru H to
allocate monthly payments (maximum for each col) and adjust the remainder to
last column. (as per below example - col G is adjusted for 2 on first
customer and col F is adjsuted for next customer and Col H is (last col)
adjusted for last customer.

Thanks.

A B C D E F G H
5 22 5 5 5 5 2
17 55 17 17 17 4
20 140 20 20 20 20 20 40

Allocation
 

in D to G

in D2 and copy across and down

=IF(MIN($B2-SUM($C2:C2),$A2),MIN($B2-SUM($C2:C2),$A2),"")

in H2:

=IF(MAX($B2-SUM($C2:G2),0),MAX($B2-SUM($C2:G2),0),"")

Copy down

There can be no formula for C2 other than =A2

"Dinesh" wrote:

Hi,

Looking for unique formula to allocate the past due amounts for customer
deliquency.

Col A = Monthly Payment
Col B = Total payments due
Col C = 30 days past due, Col D = 60 days past due etc. and goes up to Col H.

Col c thru h is not given. what is the unique formula for Col C thru H to
allocate monthly payments (maximum for each col) and adjust the remainder to
last column. (as per below example - col G is adjusted for 2 on first
customer and col F is adjsuted for next customer and Col H is (last col)
adjusted for last customer.

Thanks.

A B C D E F G H
5 22 5 5 5 5 2
17 55 17 17 17 4
20 140 20 20 20 20 20 40

Allocation
 

Hi,

Thanks for the prompt reply. The formula only works for last customer. I get
diff result for first and second customer on col g and F respectively.

Dinesh

"Toppers" wrote:

in D to G

in D2 and copy across and down

=IF(MIN($B2-SUM($C2:C2),$A2),MIN($B2-SUM($C2:C2),$A2),"")

in H2:

=IF(MAX($B2-SUM($C2:G2),0),MAX($B2-SUM($C2:G2),0),"")

Copy down

There can be no formula for C2 other than =A2

"Dinesh" wrote:

Hi,

Looking for unique formula to allocate the past due amounts for customer
deliquency.

Col A = Monthly Payment
Col B = Total payments due
Col C = 30 days past due, Col D = 60 days past due etc. and goes up to Col H.

Col c thru h is not given. what is the unique formula for Col C thru H to
allocate monthly payments (maximum for each col) and adjust the remainder to
last column. (as per below example - col G is adjusted for 2 on first
customer and col F is adjsuted for next customer and Col H is (last col)
adjusted for last customer.

Thanks.

A B C D E F G H
5 22 5 5 5 5 2
17 55 17 17 17 4
20 140 20 20 20 20 20 40

Allocation
 

Using your data, I got the same results as your posting.

Copied from my spreadsheet

A B C D E F G H
5 22 5 5 5 5 2
17 55 17 17 17 4
20 140 20 20 20 20 20 40


"Dinesh" wrote:

Hi,

Thanks for the prompt reply. The formula only works for last customer. I get
diff result for first and second customer on col g and F respectively.

Dinesh

"Toppers" wrote:

in D to G

in D2 and copy across and down

=IF(MIN($B2-SUM($C2:C2),$A2),MIN($B2-SUM($C2:C2),$A2),"")

in H2:

=IF(MAX($B2-SUM($C2:G2),0),MAX($B2-SUM($C2:G2),0),"")

Copy down

There can be no formula for C2 other than =A2

"Dinesh" wrote:

Hi,

Looking for unique formula to allocate the past due amounts for customer
deliquency.

Col A = Monthly Payment
Col B = Total payments due
Col C = 30 days past due, Col D = 60 days past due etc. and goes up to Col H.

Col c thru h is not given. what is the unique formula for Col C thru H to
allocate monthly payments (maximum for each col) and adjust the remainder to
last column. (as per below example - col G is adjusted for 2 on first
customer and col F is adjsuted for next customer and Col H is (last col)
adjusted for last customer.

Thanks.

A B C D E F G H
5 22 5 5 5 5 2
17 55 17 17 17 4
20 140 20 20 20 20 20 40

Allocation
 

I applogize for my mistake. I did not copy across, intead used the original
formula.

It works perfectly.

Thank you so much.

Regards,
Dinesh

"Toppers" wrote:

Using your data, I got the same results as your posting.

Copied from my spreadsheet

A B C D E F G H
5 22 5 5 5 5 2
17 55 17 17 17 4
20 140 20 20 20 20 20 40


"Dinesh" wrote:

Hi,

Thanks for the prompt reply. The formula only works for last customer. I get
diff result for first and second customer on col g and F respectively.

Dinesh

"Toppers" wrote:

in D to G

in D2 and copy across and down

=IF(MIN($B2-SUM($C2:C2),$A2),MIN($B2-SUM($C2:C2),$A2),"")

in H2:

=IF(MAX($B2-SUM($C2:G2),0),MAX($B2-SUM($C2:G2),0),"")

Copy down

There can be no formula for C2 other than =A2

"Dinesh" wrote:

Hi,

Looking for unique formula to allocate the past due amounts for customer
deliquency.

Col A = Monthly Payment
Col B = Total payments due
Col C = 30 days past due, Col D = 60 days past due etc. and goes up to Col H.

Col c thru h is not given. what is the unique formula for Col C thru H to
allocate monthly payments (maximum for each col) and adjust the remainder to
last column. (as per below example - col G is adjusted for 2 on first
customer and col F is adjsuted for next customer and Col H is (last col)
adjusted for last customer.

Thanks.

A B C D E F G H
5 22 5 5 5 5 2
17 55 17 17 17 4
20 140 20 20 20 20 20 40