Issues with my formula to summarize amount owed

Attached is a sample of a worksheet I made in Excel to organize the grocery expenses in my household. With how 2020 is going, my roomate and I have opted to have one person go and front the cost for groceries at the till, then split the cost at home, though either of us might be the one who goes and does the shopping.

The issue I am having is with the formula in cells I18 and I19, though allow me to explain how the sheet works first.


Section 1 (Cells A1-P15 in the sample) details and sorts the cost of the groceries so that we can see how much each cost and if it's a shared cost or something we need to buy our own. (like milk, as we each like our own kind). If more than one of each thing is bought (say 2 cartons of 2%milk).<br>The prices added are as shown on the receipt (without tax) using "=cost" so that multiple purchases of the same item can be added to keep a tally of how many times we buy something and how much each one cost.

Row 15 takes sums the columns and adds the tax for any item that is taxed (as this makes it easier to check the math using the receipts)

Section 2 (Cells A16-F22) Takes all the data summarized in row 15 and adds it up for a quick view of things to inform us of how much groceries cost each of us, and how it's divided.

Section 3 (Cells G16-N19 and the one I am having issues with) is for the accounting part:
Cells I16 and I17 are to account for how much we have spent on shopping trips (=receipt total 1+receipt total 2....)
Cells N16 and N17 are to account for how much money we paid back to each other

And finally the one I am having issues with:

Cells I18 and I19 are supposed to calculate how much we owe (in black), or how much we overspent (in red) on groceries for the month. My idea was to use the upfront cost the other person incurred (I16/17) and take away their own cost of the groceries to show if they paid either more or less than their share (signifying in turn if we owe them or they owe us). Then by also removing what we paid back(N16/17) from the total, the number would keep up to date without user error....

Well, that is unless the one entering the formula is the user error.




When I first made this sheet and tried it out with a few receipts we had on hand, and using an extra invented one as a wild card, it all seemed to work out: I18 and I19 were, as they should, exact opposites. However, with a few more trips to the store, things started to falter.... While my roomate's amount owing (let's call her Mary) was kept valid, having manually checked the figures, mine became just over $8 off. We haven't paid each other back yet and the formulas are just flipped, but something went wrong (and I tripple checked each receipt entry to see it all checks out)


After the amount of time I spent trying to solve the issue, I am sure that it must be my formula to blame, so I am asking for help from more versed people than myself to find a solution and make this work. Can you see where I went wrong?

Try changing I19 to

=B20-I16+N16-N17

As it stands, the more Mary pays, the more she owes so maybe you don't want to fix it!

I'll definitely change it, but as it stands, the N column is equal to zero, so I don't know if that's causing an issue for Tor's amount owing to be out.

I started by tracing all of the formulas, but once I suspected a problem in I19 I simply entered a value into column N. With nothing else entered in the worksheet it was clear the signs need to be reversed.

I am still having issues with this spreadsheet, though I am unsure why. As such, I did as NormS said and switched the expressions in I19, then started to fill out the form with random numbers (adding them up as I went along in different segments to simulate receipts) to give a more detailed view on the subject.

Now asside from not changing the items listed, which might make some figures seem extremely high, they should all add up and all have been accounted for.

However, as I had mentionned throughout this thread, it does show that there is a discrepency between I18 and I19 (though the amount of discrepency increases as I added more and more bills as times went on). I am still confused as to what I am doing wrong here.

In the file you attached, the sum of your receipts (Paid by Mary plus Paid by Tor) does not equal the total cost of the items purchased (Grand Total). The difference is $0.43, which is equal to the tax on the $8.65 chopsticks purchase, as well as your discrepancy between I18 and I19. Look at your total formulas in B15, C15 and D15. They add 5% to the item in row 11. A formula like that, where you make an exception for one cell in a range, can get you into trouble. Better to keep all of the taxable items together and treat them similarly.

In the attached file, I added conditional formatting to call attention to the fact that the totals don't match. I also illustrated the use of a checkbox to flag if an item is taxable, and the use of SUMPRODUCT to total up the tax on the taxable items. The tax on each item is rounded to the nearest penny so when everything is added up the totals should match.