Simple (hopefully) Excel formula required - Budget Spreadsheet

[Derek W]

Hi All,

I've put together a simple budget spreadsheet for my monthly income and outgoings / spending habits to see where my day to day spending goes. Usual stuff on there, salary, tax, bills, food, credit card etc and at the end of the month I've got a 'Profit / Loss' depending on how much i stick to saving / splurging!

I'm also wanting to see where my annual spending goes on each of my individual groups. By and large there's no issue of doing the calculation of =sum(A1:A10) and the total will appear, the difficulty lies is that this just does a complete total of all the figures but as my colulms have an 'In' and 'Out' heading i want the money going in to be separate from the money going out so that when they're totalled it gives the correct total e.g (A1 + A3 + A5 + A7 + A9)-(A2 + A4 + + A6 + A8 + A10) = Total.

My first thought is that off to the side of the spreadsheet I can do separate totals then take one from the other but I was wondering if there was one formula to do both?

 

[Derek W]

I don't think i was overly clear. I have monthly columns with income and outgoings that deals with my general expenditure so i have monthly totals of my various expenditures over the course of the month for each month. Horizontally I want to see what the total spends are for each of my headings so my total salary will line up over the course of the 12 months, my total bills, my total food outlay. It only really gets a bit tricky if I'm transferring cash from one account e.g Premium bonds or if i 'bank' any winnings from gambling. So for example if i spend 500quid on gambling but I win 200 quid if i total up the horizontal line with all my gambling it'll say I've spent 700quid rather than a 300 quid loss so thats why I'm looking at a calculation which can total up alternative columns to give me the correct total.

EDIT

I also know that gambling is bad!

 

[Derek W]

I should have put this in the first post!

So I've got my running totals over each month at the bottom so i can see what I'm doing there. As above each month has an income and outgoings column. What I'm trying to get is the totals for the 12 months in each of the horizontal headings so that i can have a further breakdown of where I'm spending my cash.

 

[Derek W]

Ok I'll try again here.

The boxes marked in red I've already got my sums sorted out for the running totals per month. I want to add the figures highlighted in Green added each month and in the same 'total' I want the figures highlighted in blue subtracted away. So if the Gambling heading has January £50 out, February £25 out but I win £125 I would put the £125 into the In column where as the rest is in the outgoings column. If i did a simple =sum(C:27:G27) formula I'd get a total of £200 instead of £50. I would be putting these totals after the December columns at the end of the spreadsheet.

 

[Derek W]

That wasn't a joke ...

Oddly enough I'm confortable with my gambling. I know that I'll be at a loss in the long run but I'm fine with that. I don't do what I would class as 'big boy betting' but in the grand scale of things its not really why i opened this topic.

 

[Derek W]

You know since Excel got Pivot tables and the like you are probably far better off doing all the data in one worksheet. Columns something like:
Date - full date including year
maybe Month (using a formula from date - not required for Pivot tables but handy for the various sometingIFs functions)
What
Category
Money In
Money Out

Then turn that to table (Ctrl-T) and create a pivot table from that.

Pivot tables are really good what-ifs and filtering with sliders etc.

I'm just watching a tutorial on that type of thing. I've done a quick fix with what i suggested in an earlier post and divided each heading into money in and money out and then subtracted one from the other to get additional totals for each item listed.

 

[Derek W]

I know this isn’t a gambling post, but, try outplayed.com.

Play the bookies at their own game.

Good luck with the spreadsheet, I did my own about 8 years ago on google sheets and it was invaluable. Albeit not concise, it was quite large with multiple different equations but did the job!

I'd personally just ignore the mention of the gambling side of the 'discussion' Everything i have listed on my headings because in general I'm happy with the spending side of things and I'm fully aware that removing the gambling element would save me on average £700 a year cos its just the lottery and postcode lottery. I do have other gambling things but they're free bets so I'm not even spending anything but I can win stuff back. Also if we're going for extreme savings side of things, I'd probably remove the £15 a month i put towards the Guide Dogs since charity begins at home! Although radderfire highlighted potential areas for savings, I've incorporated savings and investments in my spreadsheet already.

As an example, I spent over £6k in food and drink last year. I think that sounds a lot so I'd like to see how things compare this year.

As a footnote to my spreadsheet I do also have this written :-

1. Can I cut this out or cancel it completely?
2. Can I live with less of it?
3. Can I get the same thing cheaper from somewhere else?