Formula for calculating ongoing payment arrears????

[gareth1904]

Is there a way of creating a formula that will give me an ongoing (live to that date) figure of payment arrears. For example If I have a amount due column and an amount paid column is there a way of inputting a formula that will constantly update and give me an end figure if both columns have figures inputted into them over a period of time???? Dont know if that is clear or not.

Any suggestions would be helpful

Thanks

[gmr4evr1]

I think I can help you out, but, can you post a sample of the expected outcome. You could use a few different formulas to get what you are looking for.

[gareth1904]

Hi

It would be a real help and save me infinite time if you could. Its driving me mad!!!!

Attached is a small sample

Thanks

sample.xlsx

[gmr4evr1]

Something like this?
Attachment 384405

[gareth1904]

Sorry mate cant open that attachment can you re post it please

[gmr4evr1]

Hmmm. I cant open it either. Here you go, I put the totals in column E
Total Arrears.xlsm

[gareth1904]

Hi Mate

Yeah that's sort of what I was after, but it need to be dependent on time. If looking at the spreadsheet it needs to be assumed that the date is for instance 20th Aug 10, so the amounts due for Sept, Oct Nov and Dec aren't due yet, so aren't physically arrears yet, so should get included in the total. It needs to update as time passes if you see what i mean. Not sure if its even possible!!

Thanks

[gmr4evr1]

Ok, made changes, see if this is what you wanted.
Total Arrears(2).xlsm

[gareth1904]

Hi mate thats great, its soooo close to what im after, but it doesnt completely work. I ve re attached the file to show you.

Cheers

Total Arrears(3).xlsm

[gmr4evr1]

I am working on a solution, but for whatever reason, my multiple SUMIFS are not working the way they should....or at least the way I think they should.

[gareth1904]

Ok mate

Many Thanks

I thought youd crackedit till i started playing with it!!!!

[gmr4evr1]

My bad, I didn't take into consideration that there might be partial payments.

[gmr4evr1]

I think I might have it figured out, but I had to use a helper cell to do it. See attached, highlighted cells are the changes/additions...

[gareth1904]

Hi Ive had a look and I'm not entirely sure what figures that formula is producing. I have put in red box what result we should be aiming for at this point (TODAY).
The Flat one amount seems to add the £15.00 to the arrears rather than taking it into account. The Flat 2 amount I cant work out at all what its relating too.Sorry mate!!!
Total Arrears(3).xlsm

[gmr4evr1]

My fault, I had it calculating the wrong column....See corrected attachment

[gareth1904]

Cheers Mate, looks good this time. Im guessing that the way the formula is written I will need to drag the E column down each month???

[bebo021999]

This formula will be placed in E7 then drag down the column till last row.
It will work with separate area for each Flat automatically (means E7 works from row 8 till row 19)
Note: th ere must be an emty row between 2 flats, i.e row 20

=IF(ISTEXT(A7),SUMPRODUCT((INDEX(A8:INDEX(A8:A$85,MIN(IF(ISBLANK(A8:A$85),ROW(A8:$A$85)-MIN(ROW(A8:$A$85)),"")),),)<=TODAY())*(INDEX(C8:INDEX(C8:C$85,MIN(IF(ISBLANK(A8:$A$85),ROW(A8:$A$85)-MIN(ROW(A8:$A$85)),"")),),)-INDEX(D8:INDEX(D8:D$85,MIN(IF(ISBLANK(A8:$A$85),ROW(A8:$A$85)-MIN(ROW(A8:$A$85)),"")),),))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[gareth1904]

Hi Mate

Just beeen thinking about it and was wandering if there was a way of doing something with these arrears figures once they been generated. If that spreadsheet was for one block of flats , but I also have another 90 blocks of flats, all with seperate spreadsheet files set up in the same way, all generating individual arrears. Is there a way of a single spreadsheet/list of arrears from everyone being automatically generated in a seperate sheet/file??

HOpe you understand what Im getting at!!

No worries if this is not possible.

Cheers

[gareth1904]

Many Thanks I will have aplay around with this.

Cheers

[gmr4evr1]

Cheers Mate, looks good this time. Im guessing that the way the formula is written I will need to drag the E column down each month???

Yes, or copy paste.

Hi Mate

Just beeen thinking about it and was wandering if there was a way of doing something with these arrears figures once they been generated. If that spreadsheet was for one block of flats , but I also have another 90 blocks of flats, all with seperate spreadsheet files set up in the same way, all generating individual arrears. Is there a way of a single spreadsheet/list of arrears from everyone being automatically generated in a seperate sheet/file??

HOpe you understand what Im getting at!!

No worries if this is not possible.

Cheers

If I understand correctly, you would have to create yet another sheet with a list of all the flats in one column and the arrears in another column. In the arrears cell say for flat 1 you would have a formula such as =Sheet and cell# of the cell that flat 1 arrears calculation is done.
I hope that makes sense, if not, let me know