Excel Wizards?

[Catrin]

This is a simple yet embarrassing matter. I keep a detailed budget in Excel, and I extend it for the calendar year. It helps me to see what long-term some extra expenditure in February might have in October. I've a tight budget, and I "assign" every dollar to one category or another so it matters. I am digging my way out of some...difficulty and I am making good progress so this spreadsheet has got to be trustworthy - for several reasons.

I balance my checkbook once a week, and I reconcile checkbook, Excel spreadsheet with my transactions online at the same time.

The problem is even though I have the exact same transactions in my checkbook and spreadsheet - the final total does not agree. I've triple-checked everything and they just do not. I've the same starting amount, the exact same transactions and so forth. I've exhaustively checked all of the transactions via online access to my checking account, and there is nothing there that isn't reflected in my checkbook and spreadsheet.

My spreadsheet is simple yet comprehensive, it accounts for the remaining balance from the previous pay period, the planned expenditures (confirmed as they are spent), and incoming funds. The problem is that my spreadsheet consistently tells me that my final balance is higher than it actually is.

My formulas are simple, and correct. I've checked all of the apparent empty cells to make certain they are really empty. I've checked that the formulas reference the appropriate cells.

What am I missing? I have to be...and this is really bugging me. I've got to trust this spreadsheet...

[Veronica]

Check that your formulas cover the range of cells you're using.

Veronica

[withm]

What is the difference between the two numbers? If it is divisible by -9 you have simply transposed numbers somewhere.

[withm]

As you check each entry to be sure the numbers are correct, highlight the Excel cell in a different color, and make a check mark on the printout of whatever you are comparing it to. At the end you may have an unchecked or unhighlighted entry that will be the culprit.

Also, make sure that everything to be subtracted was subtracted and not added by mistake. Is there a "minus" sign or parenthesis preceeding every entry that is to be subtracted?

The value of the difference between the actual vs. expected balance may be the amount of some regular payment that you forgot to enter.

And of course, as V suggested, make sure the ranges specified in your formulas are correct.

[withm]

Ha ha. Great minds....:)
Wow - we both posted at 10:30. I had not seen yours while i was writing mine.

[smilingcat]

look at the difference between your spread sheet, your checkbook and bank statement.


if the difference is twice one of the entries, then you've added instead of subtracting a check you wrote.
if the difference is one of the entries then, the banks have not recorded the transaction
if it is some odd number then, did you leave out one of the numbers?
entered $9.45 instead $99.45 easy to do and hard to catch.


If you still have a problem, narrow down the mistake by checking at the middle of the week instead of at the end of the week. If your halfway point is okay then the mistake is in the later half of the week, if the mistake shows up halfway through, then the mistake is in the first half of the week.

I use the sum function "=sum($b$2: $b$25)" kind of thing. use positive number for deposits and minus sign when I withdraw. This way, I'm not dependent on writing out some big equations.

[Melalvai]

The most common mistake I make is adding or subtracting something twice.

It can happen that the bank makes a mistake. We switched banks recently because the old bank's online banking system had some sort of glitch where every now and then it would decide that our starting balance when we opened the account was something other than what it was. We literally had to go ALL the way back through our transaction history to figure that out. I got to the very first transaction ever, and wait a minute, it was the $100 deposit to open the account and the online banking statement said our balance was $39 at that point! Since when was 0 + 100 = 39??

Another thing that can help is go do something else, take a walk or go for a ride, and come back to it in a bit. How many times have I torn out my hair trying to find the mistake, I finally have to go fix supper or something, and when I get back to it the mistake just jumps out at me. Where the heck was it hiding the whole time I was searching for it?

Good luck, hope you get it figured out.

[Catrin]

After three days of crossing my eyes to try and find this, I succeeded! I had accidentally recorded a payment in my checkbook (from the spreadsheet) that doesn't happen until 2/3 - that will do it! Things now balance properly, and thank you again for your tips! Not all of my payments have hit my account as of yet so I didn't catch on that one of the recorded payments in my register is for later...