Excel formula for spend tracking

B0bbyEwing

Change tracking isn't really an issue to be honest. There's only 2 of us. 

Maybe there's a benefit to change tracking but right now I can only think of it from the what did you do that for angle or a you thought you could sneakily buy that did you angle. Neither of these will be relevant in our situation. Like I say though, there's perhaps other benefits that my tiny mind hasn't thought of. 

All I'm getting now is budgeting spreadsheet videos in my YouTube feed lol. 

I can't help but feel like I could whip up something better than all of the ones I've seen. Maybe not better for you guys but better for my own situation. The problem just being not knowing how to put it together. Watching people on videos pull formula like it's no problem but its on bar with me speaking Chinese when the only Chinese I know is chicken satay with fried rice.

WillowLea1962

B0bbyEwing said:

Change tracking isn't really an issue to be honest. There's only 2 of us. 

I can't help but feel like I could whip up something better than all of the ones I've seen. Maybe not better for you guys but better for my own situation.

I agree, you can and should do it yourself and slowly make it better over time as you learn new skills with Excel/Sheets. Really simple to start with. Try new features and see if they help. That way it will always make sense because you built it. 

getmore4less

The time you spend trying to put together custom tool with constantly tweeking would  be better spent setting up MSMoney(or other) and entering the data you can't automate.

The way to accelerate understanding what you want is to do the historical data even though sparse you will get to whats missing quicker and get a feel for what categories you need and want.

How's the budget planning that's another good starting point that breaks down how you want to allocate the income

B0bbyEwing

Right I think I'm going with the Google Sheets template. Overall not happy with it totally but it'll do the job & it's better than faffing around forever & a day looking for the right one & never finding it.

So why am I back?

Well I was having a play & noticed 2 things & wondered if any of you had any feedback?

See where it says on the right "$500 saved this month". There's a condition in the cell. When it's less than 0 it should say spent this month, above 0 should say saved this month - which makes sense, right?

Try getting that number to a minus & see what it says. 

As I can see this being one of those only me things, I'll tell you now - it forever says saved this month. Never says spent, even at a minus number.

So I take the formula & use it in a brand new sheet - put a minus number in the cell & it works. Says "spent this month". So not sure why it doesn't work in the template.


Another issue I have with Google Sheets in general is the dollar default. 

I had a look how to default it to £ - go to the cog, change the language to English UK. Did this but doesn't make a difference. Open a new Google Sheet - still dollars.

Go to my other Google account, open a Google Sheets document & it's in £ as default. Check the language & it's English UK.

Yet I've just switched to English UK in my main Google account & it makes no difference at all.


Well I've sussed the saved/spent. It's because the formula is wrong. It's not J15 after all. It's I15:K15 as it's been merged across 3. Once I realised that it fixed it.

Can't fix the currency though.

Jenni_D

The template cells will likely have a $ format applied to them ... you probably need to change the format in the template rather than at a system level.

Rather than make us go look through all the replies in this thread, please link us to the Sheets template.

k_man

The other place to check is the Locale for the spreadsheet.

File - Settings

Settings for this spreadsheet.
General
Locale  This affects formatting details such as functions, dates and currency.

B0bbyEwing

Ok I should've given a clearer picture. My fault.

In terms of it being default dollars, forget templates for a second, so you don't need to find any template.

It happens on a blank spreadsheet. This is even after I've changed my language to UK English & deleted American English in the settings.

I've looked at the language settings between my account that shows £ for a new spreadsheet & my main account which shows $. 

The one that shows £ was listed as UK English. If i ever changed that then I can't remember when but that account is very old - as in back when you couldn't just sign up for gmail, you had to be invited to get a gmail address kind of old.

The other one which is more my main/official one now which shows $ was listed as American English but I've switched that to UK English yet it seems to make no difference as far as default currency goes.

B0bbyEwing

My post made me curious. 

Had a look to see when that old account was created. 27th November 2004.

Had a look when Gmail started - 1st April 2004 allegedly. 

Not the first guy to have one obviously, but pretty old all the same. Am sure some of the techie folk in here will have older gmail accounts than me. Wonder who's got the oldest.

flaneurs_lobster

B0bbyEwing said:

My post made me curious. 

Had a look to see when that old account was created. 27th November 2004.

Had a look when Gmail started - 1st April 2004 allegedly. 

Not the first guy to have one obviously, but pretty old all the same. Am sure some of the techie folk in here will have older gmail accounts than me. Wonder who's got the oldest.

Interesting.

How do you know when that old account was created? Do you still have the "Welcome to Google" email?

My earliest Gmail I can find is Feb 2005 (spam, natch). My hotmail account is earlier. 

Heedtheadvice

Language setting and cell currency format are not the same thing!

By all means set to uk English but look at the other answers in light of this clarification?