Something for lovers of Excel

[Vicario]

I have an old-style gas meter (metric). I've been trying to work out my next bill on a weekly basis by doing the rather involved maths that the British Gas site details
Take the metric units used, multiply by 1.02264, multiply by 39 then divide by 3.6. Multiply the result by the kWh rate (10.08p) and finally divide by the answer by 100 to convert to pounds and pence. I get =(((((J20-J19)*1.02264)*39)/3.6)*P19)/100. The cell referencesJ20 and J19 are for the readings I take and the kWh rate is P19.
I then have the daily rate formula =((DATEDIF(K19,K20,"d")))*(P20/100) and this gives me the number of days since the last bill and calculates the daily charge so far. (This does work so I won't explain the cell references).
I add the two together and get my bill total on that particular day. If you're with me so far......how can I make these into one long formula which will calculate my bill on any particular day? I can set a cell to add the two totals together of course, which is probably simpler, but I spent ages yesterday trying to combine them and failed so I thought that maybe somebody here could tell me how.

 

[Roddy Young]

How about:
=((J20-J19)*1.02264*39/3.6*P19/100)+((DATEDIF(K19,K20,"d"))*P20/100)

Note that I've removed some of the brackets...

 

[Vicario]

Thanks. Yes, I tried the plus sign but it didn't work as I'd tried to enclose the whole thing in brackets, which I thought you had to do. I clearly got an imbalance somewhere. Before someone corrects me again, I do realise that I'll still need to multiply the whole thing by 1.05 to get the VAT. The multiple brackets were the result of me trying different combinations of the two formulae so thanks for sorting them out.
My spreadsheet (adding the two results) says that my bill today is £35.40 and your formula gives 35.4006464 so now I'll have to change the cell value to currency and we're there! Who needs a smart meter when you've got Excel?

 

[kidgreen61]

Surely it's much simpler to read meter, multiply CM3 by 11 to give you Kw and then multiply by your cost per Kw?
I accept X11 isn't exact, but it's near enough.

 

[Roddy Young]

The formula I wrote works in my PC. I can't see why your computer should be different.
Try copying it and pasting it into your worksheet:

=((J20-J19)*1.02264*39/3.6*P19/100)+((DATEDIF(K19,K20,"d"))*P20/100)

 

[Notch7]

Take the metric units used, multiply by 1.02264, multiply by 39 then divide by 3.6. Multiply the result by the kWh rate

Wow thats spooky, this morning I did a quick spreadsheet to calculate weekly costs of energy -and had to do a bit of googling to find the gas conversion from M3 to Kwh -although I used 40 as the calorific value.


Ive been decorating the hallway and stairwell -and my wife is drying clothes on airers, with virtually no heating weve had loads of condensation, so now weve stuck it on, but not sure how much its costing

I actually dont mind it being a bit colder, but the earache is getting too much.

 

[Vicario]

Roddy - your formula does work on my machine. I was just saying that your result is in number format and so the cell has to be told that it should convert to currency. So your result becomes £35.40 when in a cell formatted for Currency (or Accounting.) 35.4006464 = £35.40, same as my result.

 

[Vicario]

Notch - I think that the CV varies between suppliers for some reason. Mine is definitely 39.
kidgreen - you're right, but my formula (as amended by Roddy) will also read the Standing Charge and multiply it by the number of days since the last bill.

 

[JonathanM]

I think that the CV varies between suppliers for some reason. Mine is definitely 39.

The CV varies all the time, for all suppliers, sometimes several times a day.