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
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.
1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.
Tom
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
Something like this?
Attachment 384405
Sorry mate cant open that attachment can you re post it please
Hmmm. I cant open it either. Here you go, I put the totals in column E
Total Arrears.xlsm
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
Ok, made changes, see if this is what you wanted.
Total Arrears(2).xlsm
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
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.
Ok mate
Many Thanks
I thought youd crackedit till i started playing with it!!!!
My bad, I didn't take into consideration that there might be partial payments.
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...
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
My fault, I had it calculating the wrong column....See corrected attachment
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
...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.
Please Login or Register to view this content.
Quang PT
Many Thanks I will have aplay around with this.
Cheers
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???
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
Yes, or copy paste.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???
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.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
I hope that makes sense, if not, let me know
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks