We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
Energy Spreadsheets - being organised?
Comments
-
DerwentMailman said:Yes I use the free Open Office Spreadsheet and it pretty much looks after itself, aggregating a rolling 12 months data and needs only 4 things to keep it running smoothly - electricity reading (in kWH), gas reading (in m3), and calorific value of gas for the billing period (average of the daily published calorific value). The last one is needed to convert gas units (col E) into kWH of gas equivalent (col F) so has the most complicated formula but this can be found easily enough. The other formulae are mostly just adding the last 12 months to give the yearly figure which does fluctuate a little As you might see from my spreadsheet the gas has gone up but the electricity has gone down to compensate over the last couple of year. Of course you can be as fancy as you wish (as I have done) drilling it down to per day usage for each billing period as the month day count varies. And you can do a weekly read to keep a really close eye on things. Rows 2-25 are hidden because it is data older than May 2019. Figures relating to early 2020 are a little awry as I was moving from Breeze to YE (via BG)
Regards0 -
Zellah said:
The 'big 6' offer very competitive smart meter only tariffs if going through uswitch or MSE CEC, i'm paying 2.05p kWh for gas with EDF with a daily standing charge of around 20p. Despite living in one of the more costly areas for energy (North Scotland).
0 -
My cheapest big six for the Eastern region (leccy only, we dont have gas) is no 17 on the CEC list and £263 more expensive so I'll stay where I amNever under estimate the power of stupid people in large numbers0
-
WBCPB said:DerwentMailman said:Yes I use the free Open Office Spreadsheet and it pretty much looks after itself, aggregating a rolling 12 months data and needs only 4 things to keep it running smoothly - electricity reading (in kWH), gas reading (in m3), and calorific value of gas for the billing period (average of the daily published calorific value). The last one is needed to convert gas units (col E) into kWH of gas equivalent (col F) so has the most complicated formula but this can be found easily enough. The other formulae are mostly just adding the last 12 months to give the yearly figure which does fluctuate a little As you might see from my spreadsheet the gas has gone up but the electricity has gone down to compensate over the last couple of year. Of course you can be as fancy as you wish (as I have done) drilling it down to per day usage for each billing period as the month day count varies. And you can do a weekly read to keep a really close eye on things. Rows 2-25 are hidden because it is data older than May 2019. Figures relating to early 2020 are a little awry as I was moving from Breeze to YE (via BG)
RegardsThat is a relatively simple job for a spreadsheet, no macros or programming required, just formulae.Here's a sample in Excel (LibreOffice will be very similar) with the tariff in a separate sheet, using electricity only, as the kWh formula is easy. The formulae are shown rather than the result. Note the absolute references (e.g. $B$2) so that the formula can be copied down to other cells:And here's the top sheet showing the result instead of the formulae:If that much makes sense, you'll be well on the way to sorting out a spreadsheet to show both utilities.I’m a Forum Ambassador and I support the Forum Team on the In My Home MoneySaving, Energy and Techie Stuff boards. If you need any help on these boards, do let me know. Please note that Ambassadors are not moderators. Any posts you spot in breach of the Forum Rules should be reported via the report button, or by emailing forumteam@moneysavingexpert.com.
All views are my own and not the official line of MoneySavingExpert.
1 -
victor2 said:That is a relatively simple job for a spreadsheet, no macros or programming required, just formulae.Here's a sample in Excel (LibreOffice will be very similar) with the tariff in a separate sheet, using electricity only, as the kWh formula is easy. The formulae are shown rather than the result. Note the absolute references (e.g. $B$2) so that the formula can be copied down to other cells:And here's the top sheet showing the result instead of the formulae:If that much makes sense, you'll be well on the way to sorting out a spreadsheet to show both utilities.That's a nice basic spreadsheet too. Probably something i'd be better off doing. Walking before I run, which would be a change.Do you have separate sheets for electricity and gas tariffs or are your tariffs on the same sheet?The whole $B$4 thing is something i've not seen before and the multiple brackets thing.You wouldn't believe that i hit Bs in Maths at school and A's in IT but with 20 years of not using any of it I struggle to recall it.Out of curiosity, when you switch supplier do you update the single tariff entry or do you create a new entry so that you have a record of what your old tariff/s cost?I do that when i track my fuel...I don't actually need to track the P.P.L. as part of the spreadsheet and formula but I do it out of curiosity, nothing else. I can see on the 16th June 2007 the diesel fill up cost me 93.9p per litre.What's the talk of macros in spreadsheets though? I know formula but macro is new to me. Only macros i know of are close ups in photography. The name rings a bell from A-level computer science but i've forgotten what it meant. IIRC it was something to do with programming but I'm likely to be off.0
-
$ references are great when similar formulas occur in multiple different locations on a sheet. I'm often making one axis absolute and the other relative, e.g. $B4.
However, you can just label cell B4 as "vatrate" and use that label in formulas.0 -
JustAnotherSaver said:victor2 said:What's the talk of macros in spreadsheets though? I know formula but macro is new to me. Only macros i know of are close ups in photography. The name rings a bell from A-level computer science but i've forgotten what it meant. IIRC it was something to do with programming but I'm likely to be off.
An energy tracking spreadsheet shouldn't need macros, in my opinion. The maths is relatively simple and can be done using formulas in relevant cells.1 -
JustAnotherSaver said:victor2 said:That is a relatively simple job for a spreadsheet, no macros or programming required, just formulae.Here's a sample in Excel (LibreOffice will be very similar) with the tariff in a separate sheet, using electricity only, as the kWh formula is easy. The formulae are shown rather than the result. Note the absolute references (e.g. $B$2) so that the formula can be copied down to other cells:And here's the top sheet showing the result instead of the formulae:If that much makes sense, you'll be well on the way to sorting out a spreadsheet to show both utilities.That's a nice basic spreadsheet too. Probably something i'd be better off doing. Walking before I run, which would be a change.Do you have separate sheets for electricity and gas tariffs or are your tariffs on the same sheet?The whole $B$4 thing is something i've not seen before and the multiple brackets thing.You wouldn't believe that i hit Bs in Maths at school and A's in IT but with 20 years of not using any of it I struggle to recall it.Out of curiosity, when you switch supplier do you update the single tariff entry or do you create a new entry so that you have a record of what your old tariff/s cost?I do that when i track my fuel...I don't actually need to track the P.P.L. as part of the spreadsheet and formula but I do it out of curiosity, nothing else. I can see on the 16th June 2007 the diesel fill up cost me 93.9p per litre.What's the talk of macros in spreadsheets though? I know formula but macro is new to me. Only macros i know of are close ups in photography. The name rings a bell from A-level computer science but i've forgotten what it meant. IIRC it was something to do with programming but I'm likely to be off.Indeed, walk before you run.That spreadsheet was just an example to illustrate the use of a formula and I just chose electricity as it is simpler than gas. If I were doing it for real, I would have both in one spreadsheet, and all the tariff data on another sheet within that file, as illiustrated.In my actual spreadsheet, I do keep a history of all the tariffs I've been on. When a new one starts, I adjust the formulae to use the new tariff data and leave the old ones in place, so that I have a full and accurate history of my costs.The use of the $ in cell references allows you to make a formula that you can copy from cell to cell, but still link to one certain cell all the time. It is well worth understanding - just look for some sort of tutorial illustrating the difference between relative and absolute cell references.The use of brackets is important to appreciate too. Blow the cobwebs out of your school maths lessons, and it might come back! They both control and explain the order of how a sequence of operations in a formula is carried out. Apologies for going back to basics, but this is an example of how brackets work...Suppose you have 1, 2 and 3 in cells A1, A2 and A3 respectively and you wanted the formula in A4 to be A1 plus A2 multiplied by A3, giving 1 plus 2 multipled by 3, which would be 3 multiplied by 3, or 9.So you put =A1+A2*A3 in cell A4 and get the answer 7.Why? Because of the "precedence" of operations. Multiplication has precedence over addition, so the formula is executed as 1 plus (2 multiplied by 3), where 2 is multiplied by 3 before 1 is added, which gives you 7. See how the brackets illustrated what was happening.The result would be the same if entered as =A1+(A2*A3).If you wanted 9, the formula would have to be =(A1+A2)*A3So, sometimes, brackets are a necessity, other times they just help make the formula more understandable.As for macros, they are a way of automating tasks you do repetitively, and are not needed in such analysis.By the way, your spreadsheet shows what is presumably the starting and ending mileage in each row (2nd and 3rd columns). If column 2 is always the same as column 3 in the row above, it is redundant. You can delete the column and make any formulae that refer to it (such as the elapsed miles) refer to the cell above in column 3.Without seeing the formulae, there are probably other redundant cells too, or you've entered data where it could be calculated from others. Your indication of the PPL is a bit strange too, but ultimately, it is what works for you. There are no hard and fast rules about how you must do things, but there are ways to make life easier.Oh dear, I sound like a teacher!!
I’m a Forum Ambassador and I support the Forum Team on the In My Home MoneySaving, Energy and Techie Stuff boards. If you need any help on these boards, do let me know. Please note that Ambassadors are not moderators. Any posts you spot in breach of the Forum Rules should be reported via the report button, or by emailing forumteam@moneysavingexpert.com.
All views are my own and not the official line of MoneySavingExpert.
1 -
victor2 said:By the way, your spreadsheet shows what is presumably the starting and ending mileage in each row (2nd and 3rd columns). If column 2 is always the same as column 3 in the row above, it is redundant. You can delete the column and make any formulae that refer to it (such as the elapsed miles) refer to the cell above in column 3.Without seeing the formulae, there are probably other redundant cells too, or you've entered data where it could be calculated from others. Your indication of the PPL is a bit strange too, but ultimately, it is what works for you. There are no hard and fast rules about how you must do things, but there are ways to make life easier.Oh dear, I sound like a teacher!!Thanks for your explanation. I'm due to shift from Scottish Power to Shell on 31st December (let's see if that goes without error!) but i'll certainly be keeping tabs on consumption from now on.To explain my sheet since you asked ...If you take the date as A, the first mileage as B so on and so forth then basically the formula in say B248 is just entered as [=C247] (without the [ ]). I then highlight the bottom corner and just drag it down. All i ever actually do is enter in to the C column whatever my mileage display is when i fill up.D is the P.P.L. which serves as nothing more than a reference for a later date if i'm ever nosey and want to look back. It doesn't tie in with any formula.E is obviously the price. This is just inputted off the receipt.F is the mileage display - how many miles i've covered since the last fill up. So when i fill up, i'm the guy who's annoying to the impatient guy behind because i'm noting down my total mileage as well as the miles i've covered (for the decimal point).G is the litres put in.H is the MP.G. which after some googling i found is miles / litres * 4.5461 so basically it reads [=F266/G266*4.5461] (again without [ ]). My initial Googling took the calculation as 4.54, Googling further brought 4.544 and Googling further still brought an apparent exact number as 4.5461. Who knows.It may be able to run more efficiently or better in another way. I don't know. That was the best I could do with the knowledge I have though.1
-
Quick question for those who have their own set up... And use absolute cell reference for tarrif rates...
How do you handle when you change supplier or tarrif? Do you input new tarrif into new cell and change the formula $ reference? Or start a new sheet for each tarrif?0
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.9K Banking & Borrowing
- 253.1K Reduce Debt & Boost Income
- 453.5K Spending & Discounts
- 243.9K Work, Benefits & Business
- 598.8K Mortgages, Homes & Bills
- 176.9K Life & Family
- 257.2K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards