with the data below
<colgroup><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
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?
A | B | C | D | E | F | G | H | I | J |
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 | 91-120 | 121+ |
$ 113,718 | $ 57,827 | $ 55,891 | $ 113,718 | $ 45,606 | $ 14,652 | $ 1,650 | $ - | $ - | $ 51,810 |
<colgroup><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
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?