To find the no of days on which outstanding is cleared

[rajeshturaha]

Hi Experts


attached is Ledger copy of a Customer.
What I need to find is that the no of days the outstanding is cleared.
For Eg. The customer has a outstanding of 635580 on date 03-06-2017.
He has made a payment of 1500 on 03-06-2017, 349000 on 06-06-2017, 2000 on 06-06-2017 & 300000 on 08-06-2017 which sumups to 652500 which is greater than the outstanding of 03-06-2017, so the no of days is 08-06-2017 (-) 03-06-2017 = 5 days.
I need a single formula which will calculate as above against (by Sales In Col Particular), as shown in the example

[CAntosh]

If I'm understanding the goal correctly, I think the following formula will work. It should be array-entered (confirm with Ctrl + Shift + Enter) in G2 and filled down (to preserve your original work, it's in H2 of my attachment):

=INDEX($A$1:$A$61,MIN(IF(SUBTOTAL(9,OFFSET($D3,0,0,ROW(D3:D$61)-ROW(D2),1))-E2>=0,ROW(D3:D$61))))-$A2&" days"

The formula should return the number of days before the credits exceed the current balance. The formula currently only looks through row 61, but if you want to be able to add more rows, just change all of the "61"s in the formula to something much bigger. If you only want to show the results for particular types of entries (e.g. "To Sales"), then wrap it in an IF clause (again, array-entered):

=IF(B2="To Sales",INDEX($A$1:$A$61,MIN(IF(SUBTOTAL(9,OFFSET($D3,0,0,ROW(D3:D$61)-ROW(D2),1))-E2>=0,ROW(D3:D$61))))-$A2&" days","")

Please take a look at the attachment to see if the solution is what you're looking for:

[rajeshturaha]

Thank u CAantosh, for your quick support. I will check it and get back to u

[samba_ravi]

or
g2=IF(C2=0,"",INDEX(A3:A$61,MATCH(E2,(SUBTOTAL(9,OFFSET(D2,0,0,ROW(D2:D$61)-ROW(D2)+1,1)))))-A2&" Day(s)")

Please Login or Register  to view this content.

Try this and copy towards down