Cash allocation formula needed please!!

[fleeboing]

Hi!!

I work from my Customer Ledger on Excel. It's got the account numbers in ColumnA and the aging going from left to right in monthly brackets up to +240 days.When allocating the cash in for, say, yesterday I create a new column on the farright and vlook it up against each account number which then shows all cashcoming in against each account.

Now, what I really need to do is after I get my cash column I'd like to have a formulathat allocates this cash against each transaction into the aging bracket from oldestto newest i.e. from right to left.

Currently I'm going to each row where there is cash in and allocating itagainst each transaction until the payment is fully allocated.

If there was a formula, which I'm sure there must be, it would save me plentyof valuable time <o></o>

Cust. No.<o></o>

Total<o></o>

0 <= No. Days < 30<o></o>

Cash<o></o>

30 <= No. Days < 60<o></o>

Cash<o></o>

60 <= No. Days < 90<o></o>

Cash<o></o>

90 <= No. Days < 120<o></o>

Cash<o></o>

120 <= No. Days < 150<o></o>

Cash<o></o>

150 <= No. Days < 180<o></o>

Cash<o></o>

180 <= No. Days < 210<o></o>

Cash<o></o>

210 <= No. Days < 240<o></o>

Cash<o></o>

No. Days >=240<o></o>

Cash<o></o>

Cash To 10/07/15<o></o>

123456<o></o>​

147.30<o></o>​

-<o></o>

12.7<o></o>​

-<o></o>

25.45<o></o>​

25.45<o></o>​

121.85<o></o>​

121.85<o></o>​

-<o></o>

-<o></o>

-<o></o>

-<o></o>

-<o></o>

-160.00<o></o>​

123457<o></o>​

140.55<o></o>​

-<o></o>

25.12<o></o>​

88.97<o></o>​

43.54<o></o>​

-<o></o>

26.46<o></o>​

26.46<o></o>​

-<o></o>

-<o></o>

-<o></o>

-<o></o>

-70.00<o></o>​

<tbody> </tbody>

I input the cash manually calculating what is remaining from each payment in.
Where a customer has overpaid it is then input to the 0-30 Days.

Thanks for reading and appreciate any help in advance.

Kind regards

Alan<o></o>

 

[Maverick27]

You really need to elaborate on how you allocate cash against each transaction with clear, illustrated examples for a couple of Customer Nos.

More details is needed, otherwise how can we formulate the logic & apply formula/code ?

 

[fleeboing]

You really need to elaborate on how you allocate cash against each transaction with clear, illustrated examples for a couple of Customer Nos.

More details is needed, otherwise how can we formulate the logic & apply formula/code ?

Thanks for the response! Sorry About that. I would have attached an image or screenshot with more illustration / detail but It says I can't in the posting permissions.

Any way I've tried to explain myself a bit better below:

Cust. No.	Total	0 <= No. Days < 30	Cash	30 <= No. Days < 60	Cash	60 <= No. Days < 90	Cash	90 <= No. Days < 120	Cash	120 <= No. Days < 150	Cash	150 <= No. Days < 180	Cash	180 <= No. Days < 210	Cash	210 <= No. Days < 240	Cash	No. Days >=240	Cash	Cash To 10/07/15
123456	147.3	-	12.7	-		25.45	25.45	121.85	121.85	-		-		-		-		-		-160
123457	140.55	-		25.12		88.97	43.54	-		26.46	26.46	-		-		-		-		-70
							4			3	2									1
							This is the remaining amount from the €70 payment that was received.			This is the Invoice say	Then I manually input the amounts off against the invoice / transaction									I pull my Cash in here through a vlook up from a different sheet.

<colgroup><col style="width: 48pt;" span="21" width="64"> <tbody>
</tbody>

I'm guessing but I need a formula in each of the cells in the Cash columns that is looking for payments on the Cash Column on far right and offsets it against any trasaction in the aging columns oldest to newest. The calculation should stop once the payment value has been fully used.

Where the customer has overpaid their account any credit value is left in the 0-30 day column.

I understabnd if that's not clear enough, if not can you tell me how to attach a file and I can send on some screenshots?

Thanks again!

Alan