Excel formula to calculate electricity bill

Vicario

I have written an Excel formula to keep track of my usage since the previous bill - I expect that some people on this forum have done the same or similar. I can now enter my current reading and see exactly what my next bill will be, including VAT. Now the technical part: I have calculated the days elapsed since the last bill using the DATEDIF function and this reads the two dates from two cells on the sheet. I would like to incorporate this into my main formula but I can't see a way of doing this. I'd be interested to see any formulae that forum members have developed (Excel or other).

My attempt so far (which does work) is =SUM(((i4*29.64)+(j3*32.15))/100)*1.05
i4 contains a cell returning the difference between the last reading and today's
j3 contains the results from a cell calculating the DATEDIF function elsewhere on the sheet from the last bill date to today (using the TODAY() function)
29.64 is my unit cost in pence.
32.15 is my daily charge in pence.
1.05 is the VAT on the whole thing (unless Rishi has a change of heart soon).

Any comments or suggestions would be very welcome.

ETAF

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 work out KWh per day , cost perday and various other things , like over/under on Direct Debit, what the projected annual cost would be - take the last 12 months and average and various stats

I do a lot of work on other excel forums, and often
a sample sheet with examples is always very useful to attach

do you know your dayrate and KWh rate do NOT include VAT , i know when i changed supplies a while back the rate they quoted , did include the 5% !!!!!! which was confusing
but i changed from that supplier quite quickly

why SUM as you have all the calcs anyway
(((i4*29.64)+(j3*32.15))/100)*1.05

(Meter Reading Diff * KWh rate )
(Number of days * day rate )
*1.05 for VAT
dividing it all by 100 to go from pence to £'s

Last edited:

JohnW2

.... I have calculated the days elapsed since the last bill using the DATEDIF function and this reads the two dates from two cells on the sheet. I would like to incorporate this into my main formula but I can't see a way of doing this. I'd be interested to see any formulae that forum members have developed (Excel or other).
My attempt so far (which does work) is =SUM(((i4*29.64)+(j3*32.15))/100)*1.05
i4 contains a cell returning the difference between the last reading and today's
j3 contains the results from a cell calculating the DATEDIF function elsewhere on the sheet from the last bill date to today (using the TODAY() function)
I'm not sure what your problem is. It sounds as if all you need to do is to take what is to the right of the equals sign in your equation for j3, put it in brackets if necessary and then replace the j3 in the above with it.

For example, if your equation in j3 were ...
=DATEDIF(X99, Y88)
... then youre above equation would become ...
=SUM(((i4*29.64)+(DATEDIF(X99, Y88)*32.15))/100)*1.05

In that case, you don't need to put the DATEDIF(X99, Y88) in brackets, but you would have to if, for example, the equation in j3 had plus or minus signs within it. For example, if the j3 equation were ..
=DATEDIF(X99, Y88)+5
... then you would have to change you equation to ...
=SUM(((i4*29.64)+((DATEDIF(X99, Y88)+5)*32.15))/100)*1.05

Am I missing something?

Kind Regards, John

Vicario

Thanks everyone. John..(using your cell references) I used DATEDIF(X99,Y88,"d") to return the result in days. I have now changed it and it works, so many thanks. I was putting an unnecessary = sign in the formula before DATEDIF. ETAF..the whole bill is subject to 5% VAT at the moment, I believe, hence the *1.05 at the end. 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. I use my EDF Energy hub to calculate my daily/monthly usage and costs. I'm averaging between 5Kwh and 6Kwh a day in a 3 bedroom semi, so quite pleased.

JohnW2

Thanks everyone. John..(using your cell references) I used DATEDIF(X99,Y88,"d") to return the result in days. I have now changed it and it works, so many thanks. I was putting an unnecessary = sign in the formula before DATEDIF.
Glad it worked. Yes, you can only have one = sign in (at the start of) a formula. You can 'nest' functions/expressions within one another to any degree you want, but without any = signs other than the one at the start of the formula.

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. I use my EDF Energy hub to calculate my daily/monthly usage and costs.
The whole of the SUM expression (i.e. including everything within the brackets which follow the "SUM") represents just a single number, so it can go anywhere in the formula (just as if it were literally just a number). If one doesn't use brackets to over-ride default behaviour, order become important if one has a mixture of + and/or - and also * and/or / within the formula, the default behaviour (without brackets) being to do multiplication and division before addition and subtracting. Hence, if A2 were 2, A3 were 3 and A4 were 4, then
=A2+A3*A4
... would give an answer of 24, since it would first multiply A3 and A4 together, and then add the A2. If you wanted A2 to first be added to A3, and the result then multiplied by A4 (giving an answer of 20) your formula would have to be:
=(A2+A3)*A4
I'm averaging between 5Kwh and 6Kwh a day in a 3 bedroom semi, so quite pleased.
That's pretty good. I average about four times that (in a much bigger house than yours), but at least a third of the total is down to night-time ('cheap',E7) use of an immersion heater.

Kind Regards, John

ETAF

I'm averaging between 5Kwh and 6Kwh a day in a 3 bedroom semi, so quite pleased.
i'm in a 3 bed bungalow and we do about 8-9KWh per day on Electric and then also use gas for heating, Water & cooking Hob
so thats good - no E7

Harry Bloomfield

I have written an Excel formula to keep track of my usage since the previous bill - I expect that some people on this forum have done the same or similar.

I have a spreadsheet into which I enter my E, G and W readings each week and from that it it calculates each weeks total bill, the cost of each and attemps to predict the eventual cost for the year. You are welcome to a copy of it.

JohnW2

I have a spreadsheet into which I enter my E, G and W readings each week and from that it it calculates each weeks total bill, the cost of each and attemps to predict the eventual cost for the year.
As a matter of interest, in relation to your annual 'predictions', do you attempt to take season variation into account?

Kind Regards, John

Harry Bloomfield

As a matter of interest, in relation to your annual 'predictions', do you attempt to take season variation into account?

Kind Regards, John

Yes!

StephenStephen

I use octopus watch to keep track - it's a little complex as the unit rate for both our ingoing and outgoing electricity changes every half hour...

Vicario

JohnW2... surely your immersion heater will cut off when the required temperature is reached, so it should only be on for an hour or so at most. It shouldn't account for a third of your usage, should it?

JohnW2

Glad it worked. Yes, you can only have one = sign in (at the start of) a formula. You can 'nest' functions/expressions within one another to any degree you want, but without any = signs other than the one at the start of the formula.
I just noticed that, in my attempts to remain brief/simple, I may possibly have misled some people with my statement above ...

... just for completeness, equals signs in addition to the one at the start of a cell formula are allowed if they are a legitimate part of the syntax, for example in defining the conditional test in an IF function. Hence, for example, the following is OK, despite the second equals sign ...

=IF(B1=C1,"Same","Different")

Kind Regards, John

JohnW2

JohnW2... surely your immersion heater will cut off when the required temperature is reached, so it should only be on for an hour or so at most. ...
It obviously depends upon how much hot water has been used during the preceding day (I have an extremely well-lagged HW cylinder) but, yes, usually "an hour or two", probably most commonly nearer 2 hours than 1 hour - see graph below for a recent night
It shouldn't account for a third of your usage, should it?
It's down to the fact that the immersion is (when using energy), easily the greatest single load in my house which is 'on' for significant amounts of time.

My total electricity consumption is around 20 kWh/day. The (3 kW) immersion is 'powered' for the entire E7 cheap-rate period (roughly 01:30 - 08:30 BST at present), so if it were continuously using energy continuously during that period (i.e. if there were no thermostatic control, and it was left to 'boil'!) it would, alone, use 21 kWh per day, around the same as my installation's total consumption. 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.

Kind Regards, John

Last edited:

Jackrae

Are you people trying to depress me !
My daily usage averaged over the year is 17kw for E and 98kw for G.
And a few tonnes of wood to boot
That, I suppose, is the price for having a large old stone house
No chance of getting a sensibly priced heatpump to handle that load whilst maintaining quality of life

Harry Bloomfield

My daily usage averaged over the year is 17kw for E and 98kw for G.

7Kw per day for E and 40Kw for G, averaged over the last full year, last year Nov20 to Nov21.

DIYnot Local

Staff member

If you need to find a tradesperson to get your job done, please try our local search below, or if you are doing it yourself you can find suppliers local to you.

Are you a trade or supplier? You can create your listing free at DIYnot Local

Replies
79
Views
2K
G
Replies
8
Views
1K
Goldspoon
G
N
Replies
5
Views
851
tonyelectric
T
M
Replies
6
Views
468
J
Replies
12
Views
1K