Results 1 to 5 of 5

Thread: Formula to pay one expense first

  1. #1
    Seeker Dougj's Avatar
    Join Date
    Oct 2018
    Posts
    15
    Articles
    0
    Excel Version
    2007 office365

    Formula to pay one expense first



    Register for a FREE account, and/
    or Log in to avoid these ads!

    In call A1 I have a total amount received for out of pocket and labour expenses. The amount in this cell varies as payments come in.
    In cell B1 I have the total of Out of {Pocket expenses that need to be reimbursed from the total in cell A1.
    In cell C1 I have the total of labour expenses that need to be reimbursed from the total in cell A1.
    Out of pocket expenses (seen in B1) need to be paid in total before labour expenses are reimbursed. This will be shown in cell B2.
    I am trying to write a formula, with no success, that will pay off the amount owed in B1 first and once there is more in A1 than what is owed in B1 and B1 is paid in full, I then want B1 to show $0 owed and C1 to start showing it being paid off.

    For example if I have been paid $100 (shown in A1) to date, of an amount of $110 that is owed in total. As well B1 shows OOP as being owed $50 and C1 shows as being owed $60. Since I have $100 paid so far I want cell B2 to show as being owed $0 because it has been paid its $50 due. At the same time C1 should show as being owed $10 because it has been paid $50 of the A1 toial after B1 has been paid in full. My SUM's and SUMIF's done/t seem to be working. Hope I got that straight.

    Any direction is appreciated.

    D.

  2. #2
    Seeker Dougj's Avatar
    Join Date
    Oct 2018
    Posts
    15
    Articles
    0
    Excel Version
    2007 office365
    Click image for larger version. 

Name:	spreadsheet.png 
Views:	13 
Size:	42.7 KB 
ID:	10743Perhaps the attached image will better explain what I am looking to do. Columns M5 to M12 and N5 to N12 contain values, by person, that represent the amount that person is owed. Column O5 to O12 represents, by person, the amounts each person has already been reimbursed.

    Using row 5 as an example what I want to show in cell M25 is the amount of the total shown in cell M5 that is still due to person 1 as they are paid and until it shows $0.00. Once column M is paid in full (cell M25 shows $0) then I want cell N25 to begin to be paid off until it also reaches $0, at which point person 1 is lwed no more money.

    Hope that clarifies what i am looking for.

    D.

  3. #3
    Seeker Dougj's Avatar
    Join Date
    Oct 2018
    Posts
    15
    Articles
    0
    Excel Version
    2007 office365
    Can I assume that since I have received no responses to my question that this calculation is not possible or is it not explained well enough?

    Thanks.
    D.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,104
    Articles
    0
    Excel Version
    365
    Probably a bit of both; the formulae required may not be super simple, and because of this, potential responders may want to experiment, whereupon a workbook would be more useful than a picture of one (because not many people are going to want to copy out your picture into a workbook). In addition, a workbook will show which cells in your setup are calculated and which are plain values, not necessarily essential to know but helpful nonetheless in getting you a robust solution.

    It might be in M25:
    =MAX(0,M5-O5)
    and in N25:
    =IF(M25=0,N5-(O5-M5),N5)
    but would like to test, especially to see that negative numbers don't appear anywhere.
    Last edited by p45cal; 2021-12-14 at 01:23 PM.

  5. #5
    Seeker Dougj's Avatar
    Join Date
    Oct 2018
    Posts
    15
    Articles
    0
    Excel Version
    2007 office365
    Thanks p45cal for the info. I am just getting back into this so will try your formula and if needed a better explanation with spreadsheet.

Posting Permissions

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