Electricity Acc. Formula

[country rose]

Hi Guys

I have an electricity invoice which I would like to set up - the invoice is as follows :


No of days = 31days
Prev. Reading = 99966 Current Reading = 101418 Difference(Consumption) = 1452


Network charge @ 3.52 p/day for 31days = 109.12
Energy charge(0-600kwh) 600kwh @ 0.8208 = 492.48
Energy charge(> 600kwh) 852kwh @ 1.296 = 1 104.19
Sub Total 1 705.79
Vat at 14% 238.81
Total 1 944.60


The tricky part is Energy charge(0-600kwh) 600kwh @ 0.8208 as if the energy consumption was below 600kwh, lets say 500kwh, then the invoice would need to calculate 500kwh on the line with the (0-600kwh) and if it is more the 600kwh, as shown above, then the calculation needs to take the consumption figure and reduce it by 600 and then show the difference on the line(> 600kwh) and work out the amounts.

Would anyone like to have a crack at working out a formula for the above.

Many thanks
Dave

 

[country rose]

Yes correct, the dates were entered incorrectly - thank you so much for the giving of your time, and the set up example, very much appreciated - you have no idea how this is going to assist me, my faith in humanity has been restored.
Regards
Dave

 

[country rose]

Hi
May I ask another question :

Have set up an Invoice using the above example and formulas, this is a long shot - have copy pasted the Previous Reading Date formula into all the unused month cells and this working without showing 0.00 in the unused cells, this is great - have also copy pasted the formulas into all the other unused cells, this also works but the Invoice shows 0.00 in all these unused cells which makes the Invoice look rather busy - would it be possible to make a formula that would show the all the unused cells as a blank.

Many thanks
Dave

 

[country rose]

Hi
Could you assist with the following, have added 3 more lines to the invoice on sheet 1 as follows :
A B C D E
Jan Feb Mar Apr May
16A : Total 1944.60
17A : Payments 2000.00
18A : B/Forward 2019.53 1964.53 1964.53 1964.53
19A : Total to Pay 1964.13 1964.53 1964.53 1964.53

Can you assist with a formula for cell 18C 18D 18E ect.... that would only show the b/forward figure if the Current Reading Date was inserted, so when sending the client his Invoice for Feb it would not show B/Forward & Total to Pay amounts for Mar Apr & May ect.....- I hope this is clear enough.

Thanks in advance

 

[FormR]

Hi, does this help with the first question?

https://www.dropbox.com/s/jkal4nw50ovxx9a/766760-electricity-acc-formula-2.xlsx

Not sure about the second, but the principal is the same, if you only want to show the result of a formula when a certain cell (B4 for example) has a value then change the formula to =IF(B4="","",your_formula)

 

[country rose]

Hi, does this help with the first question?

https://www.dropbox.com/s/jkal4nw50ovxx9a/766760-electricity-acc-formula-2.xlsx

Not sure about the second, but the principal is the same, if you only want to show the result of a formula when a certain cell (B4 for example) has a value then change the formula to =IF(B4="","",your_formula)

Yes it does, works perfectly - thanks so much - a very small error of -0,01c does appear later on :

eg.
Jan Feb Mar Apr
Sub Total 780,10 1476,10
Payments 780,10 1476,10
Balance B/Forward - (0,00)

Total to Pay 0,00 0,00 -(0,01)

Am not sure why the (0,00) appears in the B/Forward cell - 0,00 should appear?

 

[FormR]

Am not sure why the (0,00) appears in the B/Forward cell - 0,00 should appear?

Almost certainly down to rounding, try changing the "sub total" and "total" formulas to.

=ROUND(your_formula;2)

 

[country rose]

Ok will do that, as mentioned the Invoice works like a dream, your help has been greatly appreciated FormR - have a great day - Dave

 

[country rose]

Hi
Was thinking of taking the Invoice thing a step further, if it's not to much trouble - so I have twenty Invoices to send out monthly which will all be set up as we discussed, so there will be ten sheets each with it's own invoice and each Invoice now only requires the : Reading Date Meter Reading Figure and the Payment Amount, to streamline the process even further I would, if possible, like to create one sheet where the following is recorded :
All twenty Client's Names with the Reading Date Meter Reading Figure and Payment made by the Client, would it be possible to send these directly to the Invoice Sheet of each Client, this would then save me opening twenty Invoices to record the date on them individually, so will then only have to deal with One Sheet

Thanks in advance
Dave

 

[country rose]

Hi FormR, regarding the =ROUND(your_formula;2) - have written out my formula below which i use in the sub total and ask if you could set up the round formula into my formula which is a bit long winded as am sure there is a better way of setting this up.

=IF(R17="";"";R32+R36+R37-R39-R40+R42)

Many thanks
Dave

 

[FormR]

All twenty Client's Names with the Reading Date Meter Reading Figure and Payment made by the Client, would it be possible to send these directly to the Invoice Sheet of each Client, this would then save me opening twenty Invoices to record the date on them individually, so will then only have to deal with One Sheet

This all sounds do-able but it's difficult to make suggestions without knowing how the "one sheet" is set-up and what the link would be between the values on that sheet and the other sheets, maybe you could upload an example file to a file sharing site and post the link here.

=IF(R17="";"";R32+R36+R37-R39-R40+R42)

Something like this:
=IF(R17="";"";ROUND(R32+R36+R37-R39-R40+R42;2))