Originally Posted by
belinda200
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.
Bookmarks