Formula to apply amount to array of cells until consumed

[lploy1]

with the data below

[TABLE="class: grid, width: 848"]
<colgroup><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Reported Amt Balance[/TD]
[TD]Reported Amt On Acct[/TD]
[TD]Cust Balance (calculated col D - Col E )[/TD]
[TD]Invoice Amt Due from transactions[/TD]
[TD]Fwd Aged[/TD]
[TD]Curr[/TD]
[TD]31-60[/TD]
[TD]61-90[/TD]
[TD]91-120[/TD]
[TD]121+[/TD]
[/TR]
[TR]
[TD] $ 113,718[/TD]
[TD] $ 57,827[/TD]
[TD] $ 55,891[/TD]
[TD] $ 113,718[/TD]
[TD] $ 45,606[/TD]
[TD] $ 14,652[/TD]
[TD] $ 1,650[/TD]
[TD] $ - [/TD]
[TD] $ - [/TD]
[TD] $ 51,810[/TD]
[/TR]
</tbody>[/TABLE]

Want to apply the "Reported Amt On Acct" (B) to Col J, then Col I, H, G, F, E successively until Col "B" is consumed


eg Require a formula to apply this amount, sequentially to 121+, 57827 - 51810 = 0 rem 6017 then remainder to 91-120 - not required, then remainder to 61-90 not required, then remainder to 31-60 1650-6017 = 0 r 4367, Curr 14652-4367 = 10285 etc col J to col F.


Data is being returned in 2 data sets that will be combined using excel on a regular basis

was trying IF but was a time consuming formula

Any ideas?

 

[Eric W]

Welcome to the forum.

Try:

	A	B	C	D	E	F	G	H	I	J	K
Reported Amt Balance	Reported Amt On Acct	Cust Balance (calculated col D - Col E )	Invoice Amt Due from transactions	Fwd Aged	Curr	31-60	61-90
$ -	$ -

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]91-120[/TD]
[TD="align: right"]121+[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]$113,718[/TD]
[TD="align: right"]$57,827[/TD]
[TD="align: right"]$55,891[/TD]
[TD="align: right"]$113,718[/TD]
[TD="align: right"]$45,606[/TD]
[TD="align: right"]$14,652[/TD]
[TD="align: right"]$1,650[/TD]

[TD="align: right"]$51,810[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$10,285[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]J3[/TH]
[TD="align: left"]=MAX(0,MIN(SUM(J2:$J2)-$B2,J2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in J3 and drag to the left to F3.

 

[lploy1]

Welcome to the forum.

Try:

	A	B	C	D	E	F	G	H	I	J	K
Reported Amt Balance	Reported Amt On Acct	Cust Balance (calculated col D - Col E )	Invoice Amt Due from transactions	Fwd Aged	Curr	31-60	61-90
$ -	$ -

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]91-120[/TD]
[TD="align: right"]121+[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]$113,718[/TD]
[TD="align: right"]$57,827[/TD]
[TD="align: right"]$55,891[/TD]
[TD="align: right"]$113,718[/TD]
[TD="align: right"]$45,606[/TD]
[TD="align: right"]$14,652[/TD]
[TD="align: right"]$1,650[/TD]

[TD="align: right"]$51,810[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$10,285[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]J3[/TH]
[TD="align: left"]=MAX(0,MIN(SUM(J2:$J2)-$B2,J2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in J3 and drag to the left to F3.

thanks for the help seems like it will work well.

also for the welcome to the forum, been following for a while but never posted....