+ Reply to Thread
Results 1 to 20 of 20

Formula for calculating ongoing payment arrears????

  1. #1
    Registered User
    Join Date
    03-20-2015
    Location
    isle of wight
    MS-Off Ver
    office 365
    Posts
    10

    Formula for calculating ongoing payment arrears????

    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

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Formula for calculating ongoing payment arrears????

    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

  3. #3
    Registered User
    Join Date
    03-20-2015
    Location
    isle of wight
    MS-Off Ver
    office 365
    Posts
    10

    Re: Formula for calculating ongoing payment arrears????

    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

  4. #4
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Formula for calculating ongoing payment arrears????

    Something like this?
    Attachment 384405

  5. #5
    Registered User
    Join Date
    03-20-2015
    Location
    isle of wight
    MS-Off Ver
    office 365
    Posts
    10

    Re: Formula for calculating ongoing payment arrears????

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

  6. #6
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Formula for calculating ongoing payment arrears????

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

  7. #7
    Registered User
    Join Date
    03-20-2015
    Location
    isle of wight
    MS-Off Ver
    office 365
    Posts
    10

    Re: Formula for calculating ongoing payment arrears????

    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

  8. #8
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Formula for calculating ongoing payment arrears????

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

  9. #9
    Registered User
    Join Date
    03-20-2015
    Location
    isle of wight
    MS-Off Ver
    office 365
    Posts
    10

    Re: Formula for calculating ongoing payment arrears????

    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

  10. #10
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Formula for calculating ongoing payment arrears????

    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.

  11. #11
    Registered User
    Join Date
    03-20-2015
    Location
    isle of wight
    MS-Off Ver
    office 365
    Posts
    10

    Re: Formula for calculating ongoing payment arrears????

    Ok mate

    Many Thanks

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

  12. #12
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Formula for calculating ongoing payment arrears????

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

  13. #13
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Formula for calculating ongoing payment arrears????

    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...

  14. #14
    Registered User
    Join Date
    03-20-2015
    Location
    isle of wight
    MS-Off Ver
    office 365
    Posts
    10

    Re: Formula for calculating ongoing payment arrears????

    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

  15. #15
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Formula for calculating ongoing payment arrears????

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

  16. #16
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,641

    Re: Formula for calculating ongoing payment arrears????

    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

    Please Login or Register  to view this content.
    ...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.
    Attached Files Attached Files
    Quang PT

  17. #17
    Registered User
    Join Date
    03-20-2015
    Location
    isle of wight
    MS-Off Ver
    office 365
    Posts
    10

    Re: Formula for calculating ongoing payment arrears????

    Many Thanks I will have aplay around with this.

    Cheers

  18. #18
    Registered User
    Join Date
    03-20-2015
    Location
    isle of wight
    MS-Off Ver
    office 365
    Posts
    10

    Re: Formula for calculating ongoing payment arrears????

    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???

  19. #19
    Registered User
    Join Date
    03-20-2015
    Location
    isle of wight
    MS-Off Ver
    office 365
    Posts
    10

    Re: Formula for calculating ongoing payment arrears????

    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

  20. #20
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Formula for calculating ongoing payment arrears????

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 03-02-2014, 12:56 PM
  2. Replies: 3
    Last Post: 07-29-2013, 11:31 AM
  3. COMPLICATED late payment charge calculation on arrears and fixed cost
    By tric_maq in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-04-2013, 08:02 AM
  4. Multiple 'IF' formula for calculating arrears?
    By Spellbound in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-19-2009, 08:56 AM
  5. Payment in Advance/Arrears
    By Sandy Schmid in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-04-2006, 04:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1