Simple (hopefully) Excel formula required - Budget Spreadsheet

Derek W

Derek W

Derek W

Soldato
Joined
1 Oct 2008
Posts
12,587
Location
Designing Buildings
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?
 
Afraid i'm fairly basic with Excel, but in this case I would be putting my income in 1 column and outgoings in the immediate next. Then total both columns separately and then show the difference.
 
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! :p
 
Last edited:
So you want one sum of negative values and one sum of positive values - and all values are in the same column?

You should be able to use the SUMIF function, using ">0" for one of them, and "<0" for the other.
 
Hmm. No, I've misunderstood I think. But I don't think I understand still.

You say "my columns have in and out headings". So is it two different columns? Or have you got the values in one column and whether it's an in or out in the other?
 
I should have put this in the first post!

9Yf3cbK.jpeg


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.
 
OK you could create another column between A and B as "Yearly" or something. Then use SUM for the horizontal like =SUM(C6:XFD6) for row 6 ?
 
Ok I'll try again here.

lSUrQuv.jpeg


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 said:
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.
Click to expand...

Maybe not, but presumably you want to monitor your finances better in order to be able to save money. In which case, I'm giving you some free financial advice in that the gambling activity is almost certainly (99.999% certainty) costing you, in which case you'd be a lot better off by dropping it completely with the bookies, and devising your own system of betting with yourself to gain some sort of entertainment value. As far as gambling with the bookies in an attempt to make money it will be completely pointless, you will never win, you will lose money, that's a mathematical certainty, so why not just drop it? You will also save yourself some time.
 
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.
 
KompuKare said:
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.
Click to expand...

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.
 
Lkw7TzA.png


Used your gambling example because you gave actual numbers - not because I endorse it :D;)

Using SUMIF we're saying only SUM if you match the criteria, in this case, the column header is "in" (or "out"). You don't have to create separate in/out sums, could roll into a single total by having SUMIF(the in columns) - SUMIF(the out columns).

Hope this helps
 
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!
 
Chandler_90 said:
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!
Click to expand...

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! :p 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?
 
Last edited:
Armageus said:
Rather than in or out, why not just one column and use positive or negative?
Click to expand...
I depends what you want from the sheet.

If it's purely a way to keep track of the numbers then a single column would work but if you're looking for some kind of insight then having in/out is useful.

Using gambling as an example (and because it's becoming a bit of a meme at this point :D) if the single column said +£10, you wouldn't know if that was a £1 bet with an £11 return or £1000s staked for just £10 profit.
 
You must log in or register to reply here.
Back
Top Bottom