Excel formula for spend tracking

B0bbyEwing

k_man said:

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.

Sorry, was late last night so only just checked this now.

It works yet also doesn't work. 

For example, I follow what you said (open a new spreadsheet & then do what you said) & sure enough, American is listed. Change this to UK & then the $ sign on the menu is replaced with £. 

Exit the spreadsheet, create a new spreadsheet & it's back to displaying $ as default.

I want it where it is set to £ and stays at £. So I don't have to keep making the alteration. 
It's not the end of the world as it's no time at all to follow the guide you said, but I shouldn't have to - I should be able to set it as £ and then that's it for every single spreadsheet I ever open in that Google Drive. 

Like with my old original google account.

flaneurs_lobster said:

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. 

Yes, I still have the email.

I just Googled how to tell how old my Google account is & there were 3 ways listed. That was the first one. 

If we're just talking emails in general then there'll be a ton of people having them much earlier. I never even got the internet myself until about '97 which was Freeserve IIRC. 

k_man

Yes, it is a per spreadsheet setting. But easier than having to change formats on multiple cells.

B0bbyEwing

Hopefully someone reading this can shed light on to THE answer. 

If not then I'll just have to do what you said.

YBR

To illustrate a different approach, I use an excel file that might help you without a pivot table. It's set up thus:
13 sheets, first holds summary information, the others are one per month.

On each monthly sheet the first 5 columns are set up to paste out of my bank statements because that's how I use it.
Date | Description | Amount in | Amount Out | Balance
Of course, so long as it's got the amounts this can be what you like and the formulae adapted.

In Column F I select the type (shopping, travel, insurance, savings etc) from a set of pre-defined values. The data validation uses the column headings from column G onwards.

The remaining columns - G onwards have the "type" headings in Row 1, and for the rows 4 onwards a formula,
for example, column G, row 4: 
=IF($F4=G$1,$C4-$D4,0)
To unpack that: IF (<the text in column F of this row> matches <the heading of this column>)
        then insert the amount, else use zero.

In Row 2, I sum the amounts (columns C, D and G onwards)
In Rows 2&3 I have the Min/Max balance in the account that month
and the summary page refers to these two rows.

That's all clear as mud, I'm sure, but there's nothing more complex than IF, SUM, MAX and MIN to achieve it.

B0bbyEwing

Personally I think when we start getting descriptive like that, it's one of them - a picture speaks 1000 words kind of thing.

But at the same time it's also one of them - oooh I'm not showing you mine. Bit like when talking about wages. 99% of people are so secretive & talk in vague code. 

But that's what most people seem to be like & they're perfectly entitled to be that way. That's their choice.

YBR

B0bbyEwing said:

Personally I think when we start getting descriptive like that, it's one of them - a picture speaks 1000 words kind of thing.

But at the same time it's also one of them - oooh I'm not showing you mine. Bit like when talking about wages. 99% of people are so secretive & talk in vague code. 

But that's what most people seem to be like & they're perfectly entitled to be that way. That's their choice.

I take your point, but I'm not sure a picture of numbers from my bank statements helps with the underlying stuff - the formulae - which is far more interesting.

B0bbyEwing

YBR said:

B0bbyEwing said:

Personally I think when we start getting descriptive like that, it's one of them - a picture speaks 1000 words kind of thing.

But at the same time it's also one of them - oooh I'm not showing you mine. Bit like when talking about wages. 99% of people are so secretive & talk in vague code. 

But that's what most people seem to be like & they're perfectly entitled to be that way. That's their choice.

I take your point, but I'm not sure a picture of numbers from my bank statements helps with the underlying stuff - the formulae - which is far more interesting.

People learn differently. 

Me personally, I struggle with words. I can't visualise from them. My wife gets this from me so much. Just show me, don't tell me. So then she does & it's no problem then - I can get what she's trying to say.

But it's ok, you don't need to do it - as I said, I think to kick off with I'm just going to go with the default Google Sheets monthly template.

I'm not 100% happy with it, but it's a start & should get the basics of the job done.

JohnB47

I've come to this thread very late - please excuse me if I'm saying something that's already been covered.

Regarding the dollar/pound thing, does this video help?

https://www.youtube.com/watch?v=w4Kbw6PWLlI&amp;ab_channel=Sharon%27sTutorials

B0bbyEwing

JohnB47 said:

I've come to this thread very late - please excuse me if I'm saying something that's already been covered.

Regarding the dollar/pound thing, does this video help?

https://www.youtube.com/watch?v=w4Kbw6PWLlI&amp;ab_channel=Sharon%27sTutorials

Well, it SHOULD - as that last one in the video is basically what I did. So it should make it account wide.

But it doesn't.

Not your fault.