Excel help needed!

manityphon

Hello,

I'm not sure this is the correct place or even forum for this question but I'm wondering if anyone could help me with a problem I'm having on Excel.

Basically, I'm making a spreadsheet to track my spending; on the first sheet I have a list of all my accounts - Debt A, Bank, Cash etc. on sheet 2 I have a list of all my debt repayments. And on sheet 3 income; sheet 4 expenses.

So; my first entry was a debt repayment of £90 to debt A; I managed to work out the formula needed to reduce Debt A by £90 and also the formula needed to subtract £90 from my bank account. All good so far.

I then wanted to put an income entry into my spreadsheet; it will be my wages and will paid into my bank; so I entered the amount onto my spreadsheet and this is where I'm stuck! Obviously, my intention is that as soon as I type an income it will show up on the list of accounts. The problem is my bank account already has a formula in it (subtracting the £90 from Debt A repayment) so I'm assuming the problem is putting two formulas into the one cell??

I don't know if I've explained this well enough to make it comprehensible! But, if anyone can offer any suggestions regarding the formula or a better way for me to lay out my spreadsheet that would be marvellous!

Mani

manityphon

I've just cracked it! Was very simple - I just made an autosum for the cumulative balance of incomes received and simply added that to the bank account

Pennywise

You'd be better using proper software than an excel. Why waste your time writing a spreadsheet and stressing over the formula? There's a free "cash book" downloadable from https://www.vtsoftware.co.uk. It's aimed at small businesses, but I recommend it a lot for people to record and analyse their personal finances - just delete the "business" related analysis headings and substitute them with your personal ones. Unlimited bank accounts, so you can set up mortgage, loans, credit card, savings and current accounts as bank accounts. Look at the "profit and loss account" to see your income and outgoings and look at the "balance sheet" to see the current state of your bank, loans, credit card and mortgage balances. What's better is that you can click on any total to drill down to see exactly how it is made up - far more useful than a spreadsheet!

Dave_C_2

If you are Good with Excel then it's far superior to any other programs that I've seen as it can be tailored to suit my need. Also programs such as MS money can't be used to budget and work only in the past.

What I do is have a spreadsheet with the following columns
A, date, obvious and in Excel's date formula
B, Type - type of transaction DD, internet banking, card
C, Receipts - Money coming in
D, Payments - Money going out
E, Balance - Obvious
F, Comments

Conditional formatting helps a lot

In words, balance at end of row=balance at end of row above + payments - receipts
Easily copied down the sheet for spot balances
Set up ranges for payment and receipt then overall balance = start balance + sum receipts - sum payments.

Budget easily for weekly shopping (add 7 to date)
Budget easily for monthly stuff (set up data series once for 1st od each month).
Budget easily for next year by adding 365 to the date
and so on...

Still need to work on a macro to correct spot balances after adding a row

Dave