If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Allocate payments
I am breaking an unwritten rule by re-asking a question but the first time
was late on Friday and has become lost and ignored over the weekend. At least that's what I hope as I'm sure there is a solution albeit a long question. I have a list of unpaid invoices in two columns. In A is the original value. In B is the outstanding amount. Normally payments would be allocated to the oldest debt at the top and invoices would be zeroed in B as the payments progressed. Zero invoices drop off. As often happens, some payments have been allocated to the wrong invoices and although the overall debt position is the same I cannot use the data in column B to supply an accurate statement of what is due. Whilst it is easy to identify where the payments are and how much has been paid in a separate col I would like to be able to use a formula to show the position as it should be. As col A still has the original debt and each invoice appears in it's own row it is not difficult to track down the payments and the total value. A1 -B1 = payment made or nil. The hard bit is to have Excel deduct the total of the wrongly allocated amounts from the old debts until that total is used up. Simple example A1 has original 5000.00 and B1 5000.00 bal B2 has original 5000.00 and B2 2000.00 bal A3 has original 5000.00 and B3 5000.00 bal A4 has original 5000.00 and B4 3000.00 bal etc The list can be up to 50 invoices. Here 5000.00 has been wrongly allocated and B1 is still showing as due. Any ideas? -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. |
#2
|
|||
|
|||
Allocate payments
Add two columns. One to determine the payment made, the other for the
correct balance. In this example I put some headers in row 1 Original Bal Pmt Correct Bal 5000 5000 0 0 5000 2000 3000 0 5000 1000 4000 3000 5000 1000 4000 5000 5000 4000 1000 5000 Ciolumn C (Pmt) is of course easy =A1-B1 and copy down Column D is a little trickier D2 has a unique formula =A2-(MIN(A2,SUM($C$2:$C$6))) This one is fairly simple being the first item subtract the smaller of either the total or the sum of all Pmts. D3 gets tricky =A3-MIN(A3,SUM($C$2:$C$6)-MAX(0,SUM($A$2:A2)-SUM($D$22))) This get copied down. Again subtracting the smaller of the total or remaining Pmt. The trick here is the absolute and relative references Sum($C$2:$C$6) is simply the total of all payments made Max(0,Sum($a$2:a2)-sum($d$22)) calculates the unapplied portion remaining of the total payments. The max(0, is needed because eventually this formula turns negative. Sum($A$2:A2) totals all of the original invoice values above the row in question. Sum($D$22) subtracts any correct reamining balance from this. Subtracting this from the Sum($c$2:c2) yields the unaaplied portion of the payments. -- If this helps, please remember to click yes. "Russell Dawson" wrote: I am breaking an unwritten rule by re-asking a question but the first time was late on Friday and has become lost and ignored over the weekend. At least that's what I hope as I'm sure there is a solution albeit a long question. I have a list of unpaid invoices in two columns. In A is the original value. In B is the outstanding amount. Normally payments would be allocated to the oldest debt at the top and invoices would be zeroed in B as the payments progressed. Zero invoices drop off. As often happens, some payments have been allocated to the wrong invoices and although the overall debt position is the same I cannot use the data in column B to supply an accurate statement of what is due. Whilst it is easy to identify where the payments are and how much has been paid in a separate col I would like to be able to use a formula to show the position as it should be. As col A still has the original debt and each invoice appears in it's own row it is not difficult to track down the payments and the total value. A1 -B1 = payment made or nil. The hard bit is to have Excel deduct the total of the wrongly allocated amounts from the old debts until that total is used up. Simple example A1 has original 5000.00 and B1 5000.00 bal B2 has original 5000.00 and B2 2000.00 bal A3 has original 5000.00 and B3 5000.00 bal A4 has original 5000.00 and B4 3000.00 bal etc The list can be up to 50 invoices. Here 5000.00 has been wrongly allocated and B1 is still showing as due. Any ideas? -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. |
#3
|
|||
|
|||
Allocate payments
Paul
Thanks for that. It's not working for me. I'd done the calc manually and doesn't match. I did make a mistake in my original posting - I put B2 instead of A2 in my example but you would have seen that. It's hard to describe. You have an extra row in the basic example with different figures. Is there anywhere that I can post the original data- with manual result. I've used Google docs previously but some prefer not to. Regards -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Paul C" wrote: Add two columns. One to determine the payment made, the other for the correct balance. In this example I put some headers in row 1 Original Bal Pmt Correct Bal 5000 5000 0 0 5000 2000 3000 0 5000 1000 4000 3000 5000 1000 4000 5000 5000 4000 1000 5000 Ciolumn C (Pmt) is of course easy =A1-B1 and copy down Column D is a little trickier D2 has a unique formula =A2-(MIN(A2,SUM($C$2:$C$6))) This one is fairly simple being the first item subtract the smaller of either the total or the sum of all Pmts. D3 gets tricky =A3-MIN(A3,SUM($C$2:$C$6)-MAX(0,SUM($A$2:A2)-SUM($D$22))) This get copied down. Again subtracting the smaller of the total or remaining Pmt. The trick here is the absolute and relative references Sum($C$2:$C$6) is simply the total of all payments made Max(0,Sum($a$2:a2)-sum($d$22)) calculates the unapplied portion remaining of the total payments. The max(0, is needed because eventually this formula turns negative. Sum($A$2:A2) totals all of the original invoice values above the row in question. Sum($D$22) subtracts any correct reamining balance from this. Subtracting this from the Sum($c$2:c2) yields the unaaplied portion of the payments. -- If this helps, please remember to click yes. "Russell Dawson" wrote: I am breaking an unwritten rule by re-asking a question but the first time was late on Friday and has become lost and ignored over the weekend. At least that's what I hope as I'm sure there is a solution albeit a long question. I have a list of unpaid invoices in two columns. In A is the original value. In B is the outstanding amount. Normally payments would be allocated to the oldest debt at the top and invoices would be zeroed in B as the payments progressed. Zero invoices drop off. As often happens, some payments have been allocated to the wrong invoices and although the overall debt position is the same I cannot use the data in column B to supply an accurate statement of what is due. Whilst it is easy to identify where the payments are and how much has been paid in a separate col I would like to be able to use a formula to show the position as it should be. As col A still has the original debt and each invoice appears in it's own row it is not difficult to track down the payments and the total value. A1 -B1 = payment made or nil. The hard bit is to have Excel deduct the total of the wrongly allocated amounts from the old debts until that total is used up. Simple example A1 has original 5000.00 and B1 5000.00 bal B2 has original 5000.00 and B2 2000.00 bal A3 has original 5000.00 and B3 5000.00 bal A4 has original 5000.00 and B4 3000.00 bal etc The list can be up to 50 invoices. Here 5000.00 has been wrongly allocated and B1 is still showing as due. Any ideas? -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. |
Thread Tools | |
Display Modes | |
|
|