Help to to create a Formula or Macro

[Guest]

Hello Experts,

Customer is delinquent for 10 payments of total $25,850 (Col b2)/each
monthly payment of $2585(Col a2). I extract the customers arrears from data
warehouse which populates From Current billed to 90-120days (column C2 to G2)
per below. 90-120days column which is last column has $15,510 balance. I
would like to add four more column (121+,151+,181+,211+days) extending the
delinquent amount by each monthly payment of $2,585. So the each monthly
delinquent amount is spread to 9 columns (Col c2 thru Col k2). Col K (211+)
would have $5170 balance.

Per data warehouse Extract

Col A1 -Monthly Rent Col A2- $2,585
B1-Total Arrears B2- $25,850
C1-Current Billed C2- $2,585
D1-1-30 days D2-$2,585
E1-31+ E2-$2,585
F1-61+ F2-$2,585
G1-91+ G2-$15,510

I am looking for a solution (formula for Col H2 thru k2 or macro) where Col
G2 should have $2585 delinquent amount instead of $15,510 and it should
further populate the delinquent to column H2, I2, J2 in the amount of $2585
each and K should have $5170 balance.

Thanks for the help.

Dinesh

 

[Guest]

Aren't H2 to J2: =$A2?

and K2: =$B2-SUM($C2:$J2)

Or have I missed something?

 

[Guest]

Thanks for your response.
This is the formula I have been using. Let me elaborate more on it. What if
customer is delinquent for only 9 or 8 months instead of 10 months? So
another example would be:

A2=$2054
B2=$16432
C2 thru F2=each equal to $2054
G2=8216
In above case G2 balance needed to be broken down by each $2054 from G2 thru
J2.

So I am looking for universal formula for Col G2 thru K2 and copy it to all
customers rather than analyzing each customer balance in G2. Hope it is clear.

Thank you.

dinesh

 

[Guest]

You will need a macro which will look at G2 value and divide it by A2 value
to give number of payments and then allocate these payments to cell G2 to K2
as needed.

TRY:

Sub Allocate()


Dim ws1 As Worksheet
Dim irow As Long
Dim Lastrow As Long
Dim col As Integer

Set ws1 = Worksheets("Sheet1")


col = 1 '<=== column for lastrow calculation

With ws1

Lastrow = .Cells(Rows.Count, col).End(xlUp).Row

For irow = 2 To Lastrow
n = .Cells(irow, "G") / .Cells(irow, "A")
If n <= 5 Then
.Cells(irow, "G").Resize(1, n) = .Cells(irow, "A")

Else

.Cells(irow, "G").Resize(1, 4) = .Cells(irow, "A")
.Cells(irow, "K") = .Cells(irow, "B") - Application.Sum(.Cells(irow,
"C").Resize(1, 9))
End If
Next irow

End With

End Sub