Assistance Required with Excel Calculations

[matthewhinds]

To Whom it may Concern

I've created a spreadsheet that I use for my personal expenditure.

I get paid fortnightly (14 days) but pay expenses monthly^.

Recently I've noticed that my spreadsheets have not been calculating correctly; for example, if I'm supposed to get a total of $345.22, then excel has been returning incorrect results. I'm confident my formulas are correct, but I could always be wrong.

I'd like anyone to have a look at my spreadsheet (attached) and show me any errors, corrections in formulas etc.

Here's how the spreadsheet should work:

Firstly, each tab (sheet) across bottom is labelled for a purpose, the first tab is Income and Deductions.

In the top-half of this sheet, is my income. MOST cell's - but not all will have a formula similar to =(0*2) {zero = $ amount, ie $2.22; * = multiply [you know this already], and 2 = quantity (how many? 1, 2, 3, 4, 5 etc - change to zero for none, if not required - by entering 0 instead of deleting, the formula is still there when required later)}.

In the bottom-half of this sheet, are tables pertaining to any deduction - ie Gross Income - (minus) tax; or in my case, total welfare minus any repayment (I took and advance and have to repay it back over six months).

If you look up (NOT the V/H Lookup command; but literally look it up with your eyes) Income and Deductions tab and cell C34, on the Totals tab in cell B7, it should show the result, DO NOT change (formula) $C$39, rather only change the one before ie, $C$22 to $C$34. In This instance, $C$34 = Income and Deductions tab. So on the Totals tab, $B$9=B5-B7.

On ANY tab, if a formula reads 0 (zero), it usually means that item is NOT required; eg $52.95*0 (result = $0.00).

I've recently noticed on ANY tab except TOTALS, that the total result isn't calculating correctly, for example, on the Savings tab, the result should be $303.08, but excel calculates $303.09, meaning I have to manually adjust the calculations.

On the Account Payments tab - this is where I'm really seeking someone's assistance.

On the Account Payments tab, cell E6 (Opening Balance), this = SUM('Income & Deductions'!$B$6, 'Income & Deductions'!$B$8)

The CR (Credit) Column is what comes in; per item/per fortnight, whilst the DR (Debit) Column is what goes out (get's paid); per item, per month^.

I've designed the Account Payments tab so that I'm not spending all my income at once and not having anything leftover. That is Rent, Health Insurance, and Food/Groceries, paid in first half of month - ie days 1-15, and Food/Groceries, plus everything else paid in the second half of month
ie days 16-31 (adjust accordingly). Add all of columns C and D (get separate totals for each column), using excel, then repeat using standard calculator, and this is where the problem is, EXCEL IS NOT CALCULATING CORRECTLY.

Cell B6 Income & Deductions, tab is typed in manually.

On ALL tabs (I had to manually adjust figures, ie subtract or add $0.01 here/there) as well as (on) the Account Payments tab, cell D37 (Total of column D), and cell C38 (Total of column C), gives a different answer to that shown in Cells C40/D40 (I used a proper calculator here).

Cell E39 Account Payments, tab is not only the closing balance of the current sheet, but also the Opening Balance of the next sheet.

For example, if one sheet says I must have an Opening Balance of $23.75, then I must ensure the Cell E39 (Closing Balance) Account Payments on the previous book is equal to $23.75 (or whatever amount is required.

To whomever responds, by now you're thinking WTF? The way I've designed it is really simple, it's just that my formulas aren't calculating correctly. If you have access to Messenger through Facebook, you can chat with me through there (search Matthew Hinds, Oaklands [no apostrophe] Park, South Australia). Send an email ([email protected]) to let me know day/time you'll be on (just calculate the time difference), and we can chat live if that makes it easier to explain.


^ Food/Groceries excepted (fortnightly)


Thank you, and I hope to hear from anyone.

Cheers

Matthew Hinds, Adelaide, South Australia, AUSTRALIA

[Richard Buttrey]

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[matthewhinds]

My reply to below: to Moderator, please change title to my message to something more suitable on my behalf. Thank you.

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Richard Buttrey]

My reply to below: to Moderator, please change title to my message to something more suitable on my behalf. Thank you.

With 27 posts here you really should be able to understand the request. You managed to change your 15th April 2013 post here http://www.excelforum.com/excel-form...ml#post3197767 after being asked so I don;t see why you are unable to do the same with this one.