Excel formula to calculate electricity bill

[Harry Bloomfield]

However even though (per the graph) it is (under thermostatic control) 'on' for only a couple or so of those seven hours per day, that still represents a substantial proportion of my total consumption - "a third" might have been a bit of an over-statement, but it is certainly usually more than a quarter.

Rather than having it come on at 1:45am to do the bulk of the heating, then cycling on and off several times to replace lost heat - why not if you can, have it start the bulk heating cycle at 6am?

 

[JohnW2]

Rather than having it come on at 1:45am to do the bulk of the heating, then cycling on and off several times to replace lost heat - why not if you can, have it start the bulk heating cycle at 6am?

We used to do that, but it actually makes very little difference, presumably because the total amount of heat loss during the 7-hour E7 cheap period is much the same, regardless of when one heats the water during that period. In other words, when we delayed the immersion start time by a few hours, until around 6am, the initial period of continuous heating became longer (than if it came on at about 1:30am), by not much less than the total of the 'top-ups' (each only a very small number of minutes long) with the earlier start - so the total amount of energy used was almost the same.

Whilst that approach was fine for us, the reason we changed it was because we quite frequently have family members staying here who (at least, pre-Covid) had to get up between 05:00 and 06:00 to get to work (and wanted hot showers!). That was a double whammy - having more people in the house inevitably meant that the temp of water in the cylinder was lower at the start of the night, but, in any event, starting heating around 06:00 was no good for people wanting to have showers before or soon after that time!

We could, maybe should, have changed back to the original system, or some intermediate one - but since my experiments back then indicated that (presumably for the above reason) it made very little difference, I haven't bothered. I would add that the above comments are in the context of an extremely-well insulated cylinder (probably 500-600mm all around - including above and below)!

Kind Regards, John

 

[Diver Fred]

Open Office/Libre Office Calc formula to determine number of days

in Column 'B' =DAYS(A>latest date<;A>previous date<) - e.g. =DAYS(A119;A118)

Where Column 'A' is date in format DD/MM/YYYY

 

[Adam_151]

I was always taught to put SUM at the start of a calculation. I think it was way back when Excel required that but of course it doesn't nowadays.

Sum is used to find the sum of figures in a given range of cells with the references of the diagonal corners separated by a colon, e.g. =Sum(A1:A10) or =Sum(A1:B:10) or Sum=($A$1:C10) for example.

It seemed to make no sense in your example, so I opened excel and tried a few things, and it seems that if the argument doesn't contain a colon then the sum keyword is simply ignored, which before today is not what I would have expected, I would have expected it to throw an error that it cant do a sum without an argument containing a range of cells being provided.

I Don't think its the case that they keyword was required to preceed any calculation, however although I can only really recall as far back to version 5, it would seem strange to do so however as Sum as always meant the total of two or more values, even if primary school age children might refer to doing various simple maths operations as "Doing sums". I would suggest thats its more likely that the person who taught you about excel formulas had a flawed understanding that the keyword was necessary for any maths operation.

 

[Murdochcat]

I just record the readings I give out supplier each month and make sure that our bills reflect our readings. Not much point in over thinking this. My spread sheet covering 10 years shows that our consumption hasn’t risen - last winter it was less as it was a mild winter

Having PV and a wood burner helps

What amazes me is the numbers of people who don’t understand their bills, what debit or credit means or their consumption

 

[FrodoOne]

It is a pity that one cannot post the actual Excel "Spreadsheet" on this site !

 

[Dereekoo]

It is a pity that one cannot post the actual Excel "Spreadsheet" on this site !

You can always post 2 pics of the spreadsheet one showing its layout and the other one with the formula shown in the cells, means perps have to build the spreadsheet as opposed to just picking it up and running wit it?.

 

[ericmark]

I tend to use java script as no licensing problems, however I still have not got the answer as to when the tariff changes from high to low, I change at 1 am and 4 am UTC as then either BST or UTC I am using the power during the cheap time. are shown, but to get them I end up doing a balancing act, having to go outside and down a set of outside steps, to read the meter before I can start to do any calculations.

This is still being shown, so know daily use, around 7 kWh but no accurate info as to how much is at each rate, but in real terms does it matter? If I want coffee I want coffee, and I am not waiting until 1 am to make it, we know electric costs, but knowing how much is of very little help when one still gets estimated bills, I thought whole idea of a smart meter was an end to estimated bills, but no.

From the comfort of my chair I can see the power used, but it is of little use, as it does not show how much at each rate. I have a graph which does show when used in the day, but can only see how many kWh at a set time.

But the big question is how does knowing the cost help? I will set machines with option for a delayed start to run at night, and I will turn off lights not in use, but to watch to the point where at 10 pm we say electric quota has been used, turn off telly and lights and go to bed is not going to happen.

 

[Northman]

With my spreadsheet I tend to look at the usage, rather than the costs. Here's a seasonal take:

This is a late 70's four bed detached. Daily electricity use doesn't vary much, but has dropped from approx 11kWh per day down to around 6.6kWh/day. (lights, cooking, washing, drying)
Gas usage is mainly heating so the seasonality shows up well. Back in 2019/20 we upgraded the windows and replaced the original boiler. Also fitted an unvented HW system. That, together with TRV's and modern programmer has helped reduce usage over time. The minimum gas usage in summer is probably a reasonable estimate of hot water usage.

 

[Harry Bloomfield]

Here are my graphs. The costings, do not represent the true cost, the true cost is much less. The costs were set whilst on a fixed tariff, now I've moved to the Tracker. You can see the weeks away from home, in both graphs.

 

[fixitflav]

i put the dates in and use that to work out the days
18/04/22 - 17/05/22
in a cell and then use
=IF(B64="",0,RIGHT(C64,8)-LEFT(C64,8)+1)
to calculate the number of days - and then use the dayrate

I don't see what you're doing there. If you put 18/04/22 in B4 (say) and 17/05/22 in B9, typing =B9-B4 gives the number of days. I use special date format dd-mmm-yy but it works with any format.
What is =IF(B64="",0,RIGHT(C64,8)-LEFT(C64,8)+1) doing?

 

[ericmark]

I would say IF is seeing if there is an entry, and if not then 0 and the right - left is taking one figure from the other, not sure why +1 but it depends on data you have. My electric meter shows from day fitted to present day, so to see how much used I would need to read the meter every day.

However using the info on the smart meter app, or the solar panel software, I can see how much used each day going back to when installed, just the 14th May which has a small problem, as local mice on a high fibre diet and ate the fibre optic cables in the local openreach cabinet so we lost internet.

Once a week reading maybe OK, I have some readings from the supplier so could select one of those as a start point, I do fail to understand why I have an estimated reading for March. And seems approx £2.70 per day first one, and £4.33 per day for second one.

But it averages out over May at less than £2 a day, plus standing charge so just over £2.50 a day, why worry when so low?

 

[fixitflav]

For example, if your equation in j3 were ...
=DATEDIF(X99, Y88)

I thought I could guess what DATEDIF does, but it isn't in the function list on my version of XL, which I thought was pretty much up to date. Just got DATE and DATEVALUE. But if I type in DATEDIF(B9,B4) it seems to recognise it, doesn't say "no such function", but says "You've entered too few arguments". The dialogue box only has 2 slots.
In any case I can't see any need for it, as I just said in #27, if you just subtract one date from another you get the number of days.

 

[JohnW2]

I thought I could guess what DATEDIF does, but it isn't in the function list on my version of XL, which I thought was pretty much up to date. Just got DATE and DATEVALUE. But if I type in DATEDIF(B9,B4) it seems to recognise it, doesn't say "no such function", but says "You've entered too few arguments".

The DATEDIF function has been in Excel for a very long time, maybe even 'for ever' (i.e. since Excel was first born!). The oldest version I have to hand is Excel 2000 (undoubtedly written more than 25 years ago), and it's certainly in that.

The "third" argument it is asking for is a specification of the desired units of the value it returns, the most straightforward being "D", "M" and "Y" for days, months and years respectively.

 

[Northman]

There's a rather entertaining description of DATEDIF here:

DATEDIF Function

The Excel DATEDIF function returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. Excel won't help you fill out the arguments for DATEDIF like other functions, but it will work when...

exceljet.net

"The status of DATEDIF in Excel is somewhat mysterious. DATEDIF (Date + Dif) is a "compatibility" function that comes from Lotus 1-2-3 way back in the 1990s. Although it's available in all Excel versions since that time, it will not autocomplete in the formula bar, and Excel will not help you fill in arguments for DATEDIF like other functions. In the immortal words of the late, great Chip Pearson: DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation. Yet DATEDIF remains an important function for problems that involve calculating the time between two dates."