+ Reply to Thread
Results 1 to 8 of 8

Splitting costs and dues on a montly spending chart isn't adding up...

  1. #1
    Registered User
    Join Date
    05-07-2020
    Location
    Alberta, Canada
    MS-Off Ver
    2019
    Posts
    4

    Splitting costs and dues on a montly spending chart isn't adding up...

    I am creating an excel spreadsheet to calculate monthly grocery expenses shared in the house. The idea is that 1 person would go out and buy all the groceries for the house and the bill will be split up at the end of the month if needed.

    The table concludes by:

    B C
    62 Total Shared Costs =B62/2
    63 My costs
    64 Their Cost
    65 =C62+B63 (My Total Cost)
    66 =C62+B64 (Their Total Cost)
    67 =B65+B66 (Grand total)


    Next, and where I am having issues with the formulas is to calculate how much we spent, how much we paid each other, and how much we have left owing to each other. For this, I set up the following, though I63 and I64 should be exact opposites (one shows the negative value of the other).

    H I J K
    62 They paid =bill total+... I paid to them =payment+....
    63 I Paid =bill total+... They paid to me =payment+...
    64 I owe them =B65-I63-K63+K62
    65 They owe me =B66-I62-K62+K63



    Again, all together, my goal is to calculate how much each of us owe by substracting how much our share of the bills cost, and substracting how much we have spent on groceries and how much the other has paid us.

    After the first shopping trip, everything added up, and adding a fake bill to test the waters, it seemed like I had a winner. However, a few more trips to the grocery store (and removing the test aspect), some inconsistencies are left there. Without revealing too much, the sheet says that I owe them $-41.90 (indicating that I paid more than I owe) and they owe me $32.96.


    I am still unsure where I went wrong but would be grateful for any help on my math issues. Thank you.
    Last edited by Tor_8-88; 05-08-2020 at 08:33 PM. Reason: Wrong cell noted

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Splitting costs and dues on a montly spending chart isn't adding up...

    Hi,
    You give areference to K61 while you dont show what cell K61 stands for.
    It will be better if you upload an excel sample file with numbers.

  3. #3
    Registered User
    Join Date
    05-07-2020
    Location
    Alberta, Canada
    MS-Off Ver
    2019
    Posts
    4

    Re: Splitting costs and dues on a montly spending chart isn't adding up...

    Quote Originally Posted by belinda200 View Post
    Hi,
    You give a reference to K61 while you don't show what cell K61 stands for.
    It will be better if you upload an excel sample file with numbers.
    Sorry it took me so long, I went ahead and corrected my prior post, however, the file is on the shared computer, and my roommate needed it.

    I went ahead and altered the initial question to add clarity, (Thank you for pointing that out by the way) and took your advice in making a sample file to share. Though the names have been altered, the prices have been removed (both for the privacy of me and my roommate) and the page has been shortened, I triple checked every formula to assure that they correspond to the original file (I.E. As "Total Tor" is calculated on cell B19 and not B64, the formulas referring to that cell now say B19, but in the same expression and placement as the original)

    I hope it's clear enough, but just in case, allow me to give a quick example (that can be added to the sheet):
    ____
    I, Tor, run to the store to buy milk and chopsticks. As Mary likes Skim milk, I prefer 1% and we cook with 2% rather than cream, I return home with 4 cartons (2 of the 2%) at a total of $4.50 each (including recycling and deposit), and a pack of chopsticks at $2.50+GST (5% here)

    On the sheet, I'd mark down "=4.50" in the cell D8, for the carton of skim, "=4.50" in the cell C9 for my carton of 1%, and "=4.50+4.50" in the cell B10 as that amount would be split. In Cell B11, I add "=2.50" as we decided to share this expense, and the tax is already calculated in row 15.

    The total on the receipt is $20.63, so in cell I17, I add "=20.63" (or I'd just add "+20.63" to whatever's already there, as with the others)
    ____


    Having added these numbers as I wrote this example, in cells I18 and I19, there's only a penny difference (since the shared cost comes out to an odd number, so the halfpenny is tossed) but it shows that I owe nothing to Mary (rather that I have a credit to my name) and that she owes me $10.31.


    The issue I am having is with these two cells, I18 and I19. These cells are supposed to make life easier by taking all the values given and boiling them down to a simple "You owe Mary this much" or "Mary owes you this much." Since the costs in the table, as well as the total cost on the receipt (added to I16 and I17) are accumulative (like the 2% milk was added "=4.50+4.50" rather than "=9"), I used the formula:
    =My Cost (B19)-How much I paid for the bills (I17)-How much I already paid back (N16)+N17 (to keep the cells as opposites)


    In lamen's terms, if Mary went shopping and I owed $14 on that receipt, but I went shopping and she owes me $30 on mine, then taking the $14 out of the $30, I would owe her -$16 (or be ahead by $16) while she would still owe me $16. In this way, it always should be showing the same number as a positive or negative value.


    However, though this worked at first, and past the different trials I put it through at the start of this month when I set it up, as I entered more receipts from my end, the numbers stopped balancing. I can't share more of the exact numbers than I have, but I will say this:

    I brought home 5 receipts while Mary brought home 1. After manually calculating how much she owes me on my receipts, and how much I owe her on hers, I see that I19 remains true as she does owe me that much more than I owe her. However, the value of I18 is a negative number $8,94 larger than the true value, even though it's using the same logic. I double checked the values, so I know it's something wrong in my formula, as the values should be the same.


    Any help with what I am missing and where I went wrong would be greatly appreciated.


    EDIT: After rereading this, I would like to clarify that the Total Tor amount, Total Mary amount and Grand Total are all correct as well as the Amounts Paid (in Green). I am guessing that my issue is with the formula itself, substracting the wrong values from the wrong base or something like that... kinda a "comparing apples to oranges" thing.
    Attached Files Attached Files
    Last edited by Tor_8-88; 05-10-2020 at 01:44 PM.

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Splitting costs and dues on a montly spending chart isn't adding up...

    Hi,
    Your formula in I18 is correct.
    =B19-I17-N16+N17

    However - In I19 you should use this formula:
    =B20-I16+N16-N17


    N17 should be subtracted from what Mary still owes you since it was already paid to you.
    In your formula you added it again as if she owes you this amount again.

    Attached is the sample file with fake numbers (partially based on your illustration) to test the balances.
    I think it should be fine now.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-07-2020
    Location
    Alberta, Canada
    MS-Off Ver
    2019
    Posts
    4

    Re: Splitting costs and dues on a montly spending chart isn't adding up...

    Hi Belinda200,

    I have spent the last few days cross-referencing my original workbook with the cells in the workbook I sent you and the workbook you sent me as yours seemed to work, yet the fixes you suggested did not work on mine.

    After checking every formula, I finally checked cell I17 (Paid by Tor amount) on your page and notice that it works because you increased the amount Tor paid by adding the values in the first three SUM categories.

    When I removed that addition to leave the $104... that you added Tor paid, the numbers went off again.

    Any idea what's going on?

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Splitting costs and dues on a montly spending chart isn't adding up...

    Hi,

    $104 represents the payment for the Chef Knife and the HP ink.
    In my simulation you paid the total amount. But if you subtract it from your expense, you should add it to whom it was paid by, i.e., add it to Marry. You can also split the amount as if Mary paid her own purchase ($12) and you paid only the shared expense. Anyway you cannot just ignore them as they are calculted on your total cells B16+B17+B18 and influence cells I18+I19

    Another way is to eliminate these expenses from the table assuming they didnt happen and the numbers will be balanced again.

    Any of these scenarios should result in a balanced "Owed to Mary"/ "Owed to Tor" outcome.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

    Thanks.

  7. #7
    Registered User
    Join Date
    05-07-2020
    Location
    Alberta, Canada
    MS-Off Ver
    2019
    Posts
    4
    Sorry but I am not satisfied.

    The sheet is divided in 3 sections:

    1. The break down, found consuming the top section, where each item is listed by name and cost while being divided by who's expense it is for each one. By entering the value as =xxx+xxx..., Adding each purchase time seperate, it keeps track on how many times something was bought and for how much, while displaying a clean summary per item.

    2. The Summary, found in the multicoloured section at the bottom of column A and B, that takes all the data of section 1 and wraps it up in a neat little bow.

    3. The Accounting, found beside section 2 and is the main topic of this thread. This section is to sort out the human aspect of the process.




    The issue I am having with your solution(s) stem from the confusion of what the purpose of this third section is for.

    Cells B16:B18 are *not* ignored, but irrelevant in cell I17, since they are taken care of in section 2.

    Rather, cells I16 and I17 are special cells that represent how much it cost us out of pocket to get food in the house. In other words, if Mary comes home from shopping, the total stated on the sales receipt is added to cell I16. And if I come home the next day with some more food, the total of my receipt is added to cell I17. That way, if my receipt includes stuff for me, stuff for her, and stuff for the house, there is a record of how much came out of my bank. And vis-versa.

    However, using the worksheet you sent back, if I add the next entry, "Mary returns from the store with cheese, milk and potatoes for a total of $20", though I would add "+20" to I16, that $20 would be added again to I17 because of your formula.




    To get back to the initial issue, given that:
    - I16 and I17 track the initial investment into our groceries,
    - Section 2 calculates the total cost per person and
    - Column N in section 3 tracks how much we gave each other

    Cells I18 and I19 are there to take all that info and point out how much each of us owes. As such, if the total is Zero, that means we paid our share and the cost is balanced. If there is a negative amount, it means we overpaid by that amount and if there's a positive amount, we owe that much to break even.

    With that in mind, if I owe Mary $20, she can't have overpaid anything but $20, as there are only 2 of us. However my formula to represent this seems to have a flaw as it doesn't show the balance between these two cells.



    I have tried flipping the additions to substractions, as you have suggested, but it doesn't matter when your adding or subtracting $0 (as we haven't tried paying each other back yet).

    The other issue is why this is not affecting both cells equally, but rather I19 displays the proper value and I18 does not... Using the same algebraic expression.
    Last edited by AliGW; 05-19-2020 at 01:49 AM. Reason: Please don't quote unnecessarily!

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Splitting costs and dues on a montly spending chart isn't adding up...

    Hi,

    When you write - no need to quote the previous post if it's directly under my message.
    It just adds redundant information.

    As for your request to clarify the formula in I18&I19 :

    You say:
    However, using the worksheet you sent back, if I add the next entry, "Mary returns from the store with cheese, milk and potatoes for a total of $20", though I would add "+20" to I16, that $20 would be added again to I17 because of your formula.
    I suspect that it results from the fact that I have already added the wole shared column B amount to you in cell I17 , so if you add entries there that are not paid by you - need to reallocate it properly. For assurance - if would be best if you make sure that the sum of I16+I17 will be equal to the total in row 15 (but make sure not to sum the subtotals in addition to the single column amount).

    If you still think that the formula has flaws - please upload the file with the simulation you mentioned in your post, showing the results you were getting to see what is still wrong in the file.

+ 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. Splitting Costs
    By jfrussell in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2019, 12:08 AM
  2. Please help with creating a excel chart with varying monthly dues
    By silvia4ecu in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 09-12-2016, 11:11 AM
  3. [SOLVED] Splitting Costs between half hourly time periods
    By bishbash89 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-29-2012, 05:25 AM
  4. Adding multiple costs to a product price!
    By icejack in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 08-06-2009, 07:04 AM
  5. Adding costs in a specific year
    By kanuvas in forum Excel General
    Replies: 2
    Last Post: 06-05-2008, 11:19 AM
  6. how to generate report based on adding costs of particular category
    By hoser in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-20-2007, 06:58 PM
  7. [SOLVED] Auto increment chart range montly
    By comfuted in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-07-2006, 06:10 PM

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