Go Back   The 75 and ZT Owners Club Forums > Social Forums > Social Forum
Register FAQ Image Gallery Members List Calendar Mark Forums Read
Notices

Reply
 
Thread Tools Display Modes
Old 23rd February 2017, 15:54   #1
HarryM1BYT
This is my second home
 
HarryM1BYT's Avatar
 
75 Contemporary SE Mk II 2004 Man. Sal. CDTi 135ps, FBH on red diesel, WinCE6 DD

Join Date: May 2010
Location: Leeds
Posts: 17,273
Thanks: 2,160
Thanked 2,061 Times in 1,586 Posts
Default E + G + W spreadsheet

I like to keep a close eye on my utilities costs, which I do via an Excell spreadsheet. Its a spreadsheet I found somewhere, modified and adapted to what I wanted - Weekly readings of E&G for the year, including standing charges. To that (with some help) I am now adding provision for water meter readings with consumption / cost graphs for all three and some prediction of the annual costs.

You feed the costings in on page 1, the readings weekly into page 2, then you see the output on page 2 and graphed data on page 3.

Once this version is completed and tested, might anyone want a copy?
__________________
Harry

How To's and items I offer for free, or just to cover the cost of my expenses...

http://www.the75andztclub.co.uk/foru...40#post1764540

Fix a poor handbrake; DIY ABS diagnostic unit; Loan of the spanner needed to change the CDT belts; free OBD diagnostics +MAF; Correct Bosch MAF cheap; DVB-T install in an ex-hi-line system; DD install with a HK amp; FBH servicing.

I've taken a vow of poverty. To annoy me, send money.
HarryM1BYT is offline   Reply With Quote
Old 23rd February 2017, 16:33   #2
reworht
This is my second home
 
Honda HR-V 1.6 i-DTEC SE Navi 5dr Diesel Hatchback

Join Date: Apr 2012
Location: North Yorks Moors
Posts: 14,446
Thanks: 782
Thanked 2,156 Times in 1,497 Posts
Default

Quote:
Originally Posted by HarryM1BYT View Post
I like to keep a close eye on my utilities costs, which I do via an Excell spreadsheet. Its a spreadsheet I found somewhere, modified and adapted to what I wanted - Weekly readings of E&G for the year, including standing charges. To that (with some help) I am now adding provision for water meter readings with consumption / cost graphs for all three and some prediction of the annual costs.

You feed the costings in on page 1, the readings weekly into page 2, then you see the output on page 2 and graphed data on page 3.

Once this version is completed and tested, might anyone want a copy?
Would love one thanks Harry
- I've enjoyed playing with (and making sensible use of) spreadsheets from Supercalc, through Lotus 123 and gawd knows how many versions of Excel.
__________________
[SIGPIC][/SIGPIC]

Rodney - you plonker!
... but more commonly known as Rod !
reworht is offline   Reply With Quote
Old 23rd February 2017, 17:16   #3
Jim Jamieson
This is my second home
 
Jim Jamieson's Avatar
 
Rover less !

Join Date: Jun 2011
Location: Tarbolton
Posts: 3,442
Thanks: 393
Thanked 888 Times in 542 Posts
Default

It's amazing what you can do with Excell, I've used it for several years with both home, car and business accounts.
Jim Jamieson is offline   Reply With Quote
Old 23rd February 2017, 21:37   #4
flying_pig
I believe i can fly
 
MG ZT-T

Join Date: Feb 2014
Location: Wolverhampton
Posts: 2,004
Thanks: 66
Thanked 280 Times in 251 Posts
Default

id love to have one as well
flying_pig is offline   Reply With Quote
Old 23rd February 2017, 21:57   #5
HarryM1BYT
This is my second home
 
HarryM1BYT's Avatar
 
75 Contemporary SE Mk II 2004 Man. Sal. CDTi 135ps, FBH on red diesel, WinCE6 DD

Join Date: May 2010
Location: Leeds
Posts: 17,273
Thanks: 2,160
Thanked 2,061 Times in 1,586 Posts
Default

I'll get back to everyone, when it is finished and tested.
__________________
Harry

How To's and items I offer for free, or just to cover the cost of my expenses...

http://www.the75andztclub.co.uk/foru...40#post1764540

Fix a poor handbrake; DIY ABS diagnostic unit; Loan of the spanner needed to change the CDT belts; free OBD diagnostics +MAF; Correct Bosch MAF cheap; DVB-T install in an ex-hi-line system; DD install with a HK amp; FBH servicing.

I've taken a vow of poverty. To annoy me, send money.
HarryM1BYT is offline   Reply With Quote
Old 24th February 2017, 16:37   #6
HarryM1BYT
This is my second home
 
HarryM1BYT's Avatar
 
75 Contemporary SE Mk II 2004 Man. Sal. CDTi 135ps, FBH on red diesel, WinCE6 DD

Join Date: May 2010
Location: Leeds
Posts: 17,273
Thanks: 2,160
Thanked 2,061 Times in 1,586 Posts
Default

I have sent both Rewort and flying pig an email via the forum, in order to get their email address to send the spreadsheet. Hopefully both have used valid email addresses when registering?
__________________
Harry

How To's and items I offer for free, or just to cover the cost of my expenses...

http://www.the75andztclub.co.uk/foru...40#post1764540

Fix a poor handbrake; DIY ABS diagnostic unit; Loan of the spanner needed to change the CDT belts; free OBD diagnostics +MAF; Correct Bosch MAF cheap; DVB-T install in an ex-hi-line system; DD install with a HK amp; FBH servicing.

I've taken a vow of poverty. To annoy me, send money.
HarryM1BYT is offline   Reply With Quote
Old 24th February 2017, 18:11   #7
uk_dave&gill
This is my second home
 
..

Join Date: Feb 2007
Location: Town
Posts: 4,166
Thanks: 1
Thanked 189 Times in 105 Posts
Default

I love Excel! So much time can be happily wasted coming up with formulas and macros to do stuff that you could achieve manually if you could be bothered, but where's the fun in that!

Seem to recall helping you with a time/expenses spreadsheet a while back Harry? By a while back, I mean around 6 years!
__________________
Dave
uk_dave&gill is offline   Reply With Quote
Old 25th February 2017, 13:03   #8
HarryM1BYT
This is my second home
 
HarryM1BYT's Avatar
 
75 Contemporary SE Mk II 2004 Man. Sal. CDTi 135ps, FBH on red diesel, WinCE6 DD

Join Date: May 2010
Location: Leeds
Posts: 17,273
Thanks: 2,160
Thanked 2,061 Times in 1,586 Posts
Default

Quote:
Originally Posted by uk_dave&gill View Post
I love Excel! So much time can be happily wasted coming up with formulas and macros to do stuff that you could achieve manually if you could be bothered, but where's the fun in that!

Seem to recall helping you with a time/expenses spreadsheet a while back Harry? By a while back, I mean around 6 years!
Have a vague recollection of that. My memory is not what it used to be, but then it never was

Yes I have spent endless, not entirely wasted, hours on this spreadsheet. I used to love playing about with numbers in my latter years at school, when all we had was pen, paper and log tables. It was a revelation when I went to college and I found there were easier ways, like slide-rules. As soon as calculators came out, I bought a then very expensive used LED display HP, driving 50 miles to collect it. That took me into home built micro-processor systems and a bit of programming. I sort of lost interest when everyone had a PC.

I show Jane the spreadsheet, as we both share the bills and her eyes just start rolling

In an Excel spreadsheet, what is the correct formulae to use for a column of figures to get an average value, but ignoring/ not counting the rows into which no numbers have yet been entered please?

Column is Y, the rows I am interested in are 5 to 60, there is only data so far entered into rows 5 to 18, so I want it to provide an average just those 13 rows, incrementing as more data is added in the rest?

=AVERAGE(Y5:Y60) provides an average value for all of the rows. I want to not count the ones with no data in them.

Sorted! - =AVERAGEIF(Y5:Y60,">0")

Sorry, I have just noticed I have somehow managed to post this in the wrong forum, perhaps a mod could move it to the Technology forum please?
__________________
Harry

How To's and items I offer for free, or just to cover the cost of my expenses...

http://www.the75andztclub.co.uk/foru...40#post1764540

Fix a poor handbrake; DIY ABS diagnostic unit; Loan of the spanner needed to change the CDT belts; free OBD diagnostics +MAF; Correct Bosch MAF cheap; DVB-T install in an ex-hi-line system; DD install with a HK amp; FBH servicing.

I've taken a vow of poverty. To annoy me, send money.

Last edited by HarryM1BYT; 25th February 2017 at 17:01..
HarryM1BYT is offline   Reply With Quote
Old 25th February 2017, 17:25   #9
uk_dave&gill
This is my second home
 
..

Join Date: Feb 2007
Location: Town
Posts: 4,166
Thanks: 1
Thanked 189 Times in 105 Posts
Default

Quote:
Originally Posted by HarryM1BYT View Post
=AVERAGE(Y5:Y60) provides an average value for all of the rows. I want to not count the ones with no data in them.

Sorted! - =AVERAGEIF(Y5:Y60,">0")
Darn, you got yourself sorted before I could look at this!

I can't use AVERAGEIF as I am still resolutely sticking with Excel 2003. I do have 2007 as well, but don't like the ribbon interface and also there's lots of things that 2007 will do natively that 2003 won't and finding ways to do them programatically is more fun!

This will work for 2003, and ignores not only zeros, but blanks and text as well!

=SUM(Y5:Y60)/SUMPRODUCT((Y5:Y60<>0)*ISNUMBER(Y5:Y60))
__________________
Dave
uk_dave&gill is offline   Reply With Quote
Old 25th February 2017, 21:48   #10
flying_pig
I believe i can fly
 
MG ZT-T

Join Date: Feb 2014
Location: Wolverhampton
Posts: 2,004
Thanks: 66
Thanked 280 Times in 251 Posts
Default

Just had a look on your costs and by the look of it i spend more in a week than you in a month for the past few months my weekly spendings are £50 on gas and about £12 on electrics
flying_pig is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 20:27.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Copyright © 2006-2023, The Rover 75 & MG ZT Owners Club Ltd