Formula to age data on statement

[flupsie]

I have a customer statement which I have set up and need to determine the ageing at the bottom of the statement. The payments received must be allocated against the oldest invoices first. Any credit notes must also be taken into account in the applicable period

I would like a formula to compute outstanding amount owing by the ageing showing current (current months items) 30 days, 60 days, 90 days + as well as last payment made

It would be appreciated if someone can kindly assist me with a formula to do the above

I have attached file on dropbox as it won't attach on this site


https://www.dropbox.com/s/2t2xiszmx1...eing.xlsx?dl=0

[FDibbins]

I have attached file on dropbox as it won't attach on this site

Because it's too big? If so, reduce the size and then upload here. Many members are unable - or unwilling - to access file hosting sites.

We need ONLY enough data to see what you have, and what you want, so only upload SMALL representative sample

[flupsie]

I have tried to upload file (16 kb) , but It when uploading nothing happens

Not sure why this will not upload

[FDibbins]

hmm 16 k is not big, the forum will allow up to 1000 k

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[flupsie]

Thanks for your input

I tried Firefox and it has allowed me to upload. Would not upload using Internet explorer 10

[FDibbins]

Not sure why you needed to upload a zip file for such a small workbook, but anyway

I *think* this is what you are after. See if you can adapt it for what you want.

90=SUMIFS($H$14:$H$43,$G$14:$G$43,"<"&$J$4-90)
60=SUMIFS($H$14:$H$43,$G$14:$G$43,">="&$J$4-90,$G$14:$G$43,"<"&$J$4-60)

[flupsie]

Thanks for your reply and for your help


I have tried to adapt your formula for the ageing , but the total of the ageing does not agree to the total outstanding. It would be be appreciated if you would check my formulas and amend

I am also trying to use a formula in J45 to display the last payment received but cannot get it to work


It would be appreciated if you could assist assist me