I have attached a spreadsheet that shows a summary of an account to date. I want to put in formulas to continue as new charges and payments occur. Thanks in advance - and I hope the upload worked!!
I have attached a spreadsheet that shows a summary of an account to date. I want to put in formulas to continue as new charges and payments occur. Thanks in advance - and I hope the upload worked!!
Last edited by Pushrod; 03-26-2009 at 02:12 PM.
What exactly do you mean by "formulas to continue as new charges and payments occur", do you just want formulas to calculate the cells? Are you manually entering all of that data?
I will enter New Charges, Pmts. Received, Credits Received and then want the rest of the row calculated with formulas
In column D, put =sum(D2,A3,-B3,-C3)
Sorry, I still don't know what the 30 day etc. means. If you could let me know what you want it to do, I may be able to help.
OK, New charges is easy (E21=A20). The hard part is Pmts. Received + Credits Received have to be applied to the oldest overdue first.
So let's say I put in B21 1000.00 and C21 0.00, then I want a formula to apply that total to I20. That would mean that J21 would equal 391.06, I21 would equal 708.69, G21 would equal 2775.71, etc.
It also must work even if no payment is made that month (each row is a monthly payment).
Hope this is clearer
I still cannot figure out how the numbers get calculated, if you could let me know what the calculations are that you are using to get the numbers you told me about, I might be able to figure this out, I'm sorry.
Well that is the point, I don't know how the calculations are done and that is why I am asking if someone knows the formula to get these numbers.
I tried to get some of the numbers and I found that there were errors, is there a possibility that you aren't transcribing the numbers correctly everytime? If so, I may be able to figure it out by getting most of the numbers correct.
Can you explain the data you posted? Is it supposed to represent a correct example?
Entia non sunt multiplicanda sine necessitate
I went back to the original statement supplied to me and the numbers on the spreadsheet I gave you are exactly the same.
I know this is a difficult formula as I am descent at math and vba but cannot get this one.
I don't understand what you posted, so don't have a clue how to help. Good luck.
It is a printout of a running balance of an account. Each row is a month (or 30 days later). Row 2 is when the account started.
Row 3 shows that there were new charges of 2498.03, no payment was made but a credit of 47.08 was applied to the balance due. This left 1216.24 that was not paid in 30 days so it moved to the 30 day column. The new balance due of 3714.37 is the 30 day (1216.24) + new charges (2498.03).
Row 4 there was new charges of 3937.38 but no payment or credit was made. So the 1216.24 is now 60 day. The new charge from Row 3 (2498.03) is now 30 day and the new balance is 60 day +30 day + new charges.
And so on as the rows go down. Pmts. Received + Credits Received are always applied to the oldest first.
I can't explain this any better.
That was a very good explanation, thanks. See attached -- the highlighted cell was in error in your workbook.
Wow I'm impressed, what a formula, I could never have figured that out. Thanks a lot. I did not see a highlighted cell in error and I checked all the numbers with the sample I sent you. What cell was it?
Can you give me a basic explanation in english of how the formula E2 works - I'm not that good with these formulas.
My mistake, they were all correct. See attached for an explanation.
Your the best, thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks