Electricity Acc. Formula

country rose

New Member
Joined
Mar 22, 2014
Messages
28
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
One possible way:

Excel Workbook
AB
1No of days31
2Prev. Reading99966
3Current Reading101418
4Difference(Consumption)1452
5
6
7Network charge @ 3.52 p/day for 31days109.12
8Energy charge(0-600kwh) 600kwh @ 0.8208492.48
9Energy charge(> 600kwh) 852kwh @ 1.2961104.192
10Sub Total1705.792
11Vat at 14%238.8109
12Total1944.603
Sheet1
 
Upvote 0
Thanks for the well set out reply , if i can ask one more question :

Basically I have to read 8 different electricity readings for 8 tenants so would like to create an invoice that will minimise the errors and workload, what would help a lot is if I could enter the monthly data into two cells which could give the end Total of the invoice - the two fields that change on a monthly basis are :

The Reading Dates(Current)
Reading dates are : 2014/02/08(Previous) - 2014/03/11(Current) Number of days = 31

The Meter Reading Consumption figures(Current) Previous meter consumption is 99966 - Current meter consumption is 101418

In the above example could one have a formula that would work out the number of days if the current date is inserted, so the formula must be able to use the previous dates to do this - the Consumption figures would also need to be worked out in the same way

So would be great to have the total amount of the invoice after inserting the Current reading date and the Current meter reading.

Many thanks
Dave
 
Upvote 0
if you use a defferent cell for different rate, vat and otters it will better for you
net unite= current reading - last trading
 
Upvote 0
It's not all that clear to be honest, but maybe something like this:

Also on dropbox:

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

Excel Workbook
ABCDE
2MonthJanFebMarApr
3Previous Reading Date07/01/201408/02/201411/03/2014
4Current Reading Date08/02/201411/03/2014
5No of days323100
6
7Prev. Reading98466999661014180
8Current Reading99966101418
9Difference(Consumption)1500145200
10
11Network charge @ 3.52 p/day for 31days112.64109.120.000.00
12Energy charge(0-600kwh) 600kwh @ 0.8208492.48492.480.000.00
13Energy charge(> 600kwh) 852kwh @ 1.2961166.401104.190.000.00
14Sub Total1771.521705.790.000.00
15Vat at 14%248.01238.810.000.00
16Total2019.531944.600.000.00
Sheet1
 
Upvote 0
Hi
On testing the formulas in cells C5 and C9 it will not accept the formula saying the Formula Contains an Error.

Amsure it is the , and "" contained in your formula - any suggestions on how to rectify?

Thanks
Dave
 
Upvote 0
Hi
Have downloaded from dropbox, the following error comes up in C5 #VALUE! or else all the other formulas are working.

Any reason why, sorry to be a nuisance but my knowledge of Excel is very basic.

Thanks
Dave
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top