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]

The formula works , thanks very much, and will also try and load the excel spread sheets as suggested for your viewing and suggestions - I hope I can get this to work, but will give it a bash.

 

[country rose]

Hi ForumR, am afraid i have no idea were the file sharing site is, can you guide me through this?
thanks
Dave

 

[FormR]

Hi Dave,

I use www.dropbox.com but there are many free file sharing sites out there to choose from.

You can also post screen shots directly into you reply.

I use Excel Jeanie for this: Download

Another alternative for posting your layout can be found here: http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

 

[country rose]

Hi FormR
Ok have managed to download the file into Dropbox - am a idiot with these things, but how do I attach or send this to you?

 

[FormR]

There should be a paper clip button next to your file, click this, then click "Get Link" - this will copy the URL to your clip board which you can paste into a reply here.

 

[country rose]

Hope this workshttps://www.dropbox.com/s/rd4s721vrm25xuk/Storm%20Electr.%20%26%20Rent%20%20-%20%20Jan%2014%20to%20Dec%2014%20-%20%2001%2004%202014.xls

 

[country rose]

Hi FormR
Don't know if this will work? - any joy with the one sent?

Storm Electr. & Rent - Jan 14 to Dec 14 - 01 04 2014.xls

 

[country rose]

Hi FormR
Did u receive the spreadsheet ok
Thanks
Dave

 

[FormR]

Hi Dave,

I did, but I've been a bit busy the past few days.

I honestly think you would be better going to each of the individual sheets and maintaining the data there. There would be options for having a single data entry point, for example designing a user form and using VBA to transfer the data to the appropriate sheet, but I think the learning curve could be steep.

Someone else may have a better idea though!

Sorry I couldn't help further.

 

[country rose]

Hi FormR
No problem but thank you very much for your help this far, as mentioned, lots of ground has been covered and I am very grateful for your assistance.
Regards
Dave