Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to to create a Formula or Macro
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to to create a Formula or Macro
Aren't H2 to J2: =$A2?
and K2: =$B2-SUM($C2:$J2) Or have I missed something? "Dinesh" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to to create a Formula or Macro
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 "Toppers" wrote: Aren't H2 to J2: =$A2? and K2: =$B2-SUM($C2:$J2) Or have I missed something? "Dinesh" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help to to create a Formula or Macro
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 "Dinesh" wrote: 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 "Toppers" wrote: Aren't H2 to J2: =$A2? and K2: =$B2-SUM($C2:$J2) Or have I missed something? "Dinesh" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to Create a Macro | Excel Worksheet Functions | |||
Using a macro to create a macro in another workbook | Excel Worksheet Functions | |||
I need formula help or create a macro to copy and paste value only | Excel Discussion (Misc queries) | |||
Create macro | Excel Worksheet Functions | |||
need to create a formula to create a timesheet but haven't a clue | Excel Discussion (Misc queries) |