Every month you pay electricity bill for your home or office. That’s, why you need to know how to calculate the electricity bill. This article to help understand electricity bill and analyze per unit electricity cost. You can learn how to calculate electricity bill from meter reading.

To know more about Excel formula and function you may take a tour on this  link…
Prepare an electricity bill following the condition is given below.

Conditions of The Project.

a) If the meter unit is less than equal 75, per unit price will be $3.5.

b) If the meter unit is less than equal 150, per unit price will be $4.

c) If the meter unit is less than equal 200, per unit price will be $5.

d) If the meter unit is more than equal 200, per unit price will be $6.

unit price table for electricity bill

unit price table

e) 2.5% Vat on each unit price.
f) Meter rent $10.
g) If the unit price is more than $500 demand charge will be $50. It is not the $40
h) To find out the Net bill.
i) 5% delay fine on the net bill.
j) Find the total electric bill.
Now Calculate Electric Bill on Given Per Unit Value.

For used unit calculation

For calculation Used unit, subtract previous unit from current unit.
So write the rule =C8-D8 in the E8 cell.
Now keep press “Fill Handle” under selection takes to E13 cell.

how to calculate used unit for electricity bill

used unit

For unit price calculation

For unit price calculation, we’ll follow the unit price table. And we’ll apply IF function to find out the unit price of meter.

Write down the rule in F8 cell.

=IF(E8<=75,E8*3.5,IF(E8<=150,E8*4,IF(E8<=200,E8*5,IF(E8>200,E8*6))))

how to calculate unit price in electricity bill

calculate unit price

Now keep press “Fill Handle” under selection takes to F13 cell.

Adding Vat

calculate vat

calculate vat

Adding Demand charge

If the unit price is more than $500, the demand charge will be $50. if it is not, then demand charge is $40

demand charge

For Net Bill calculation

For net bill calculation you have to sum unit price, vat, meter rent, and demand charge by sum function.

=F8+G8+H8+I8

OR

=sum(F8:I8)

how to calculate net electricity bill

For Delay fine

5% delay fine on Net Bill.

=J8*5%

Now keep press “Fill Handle” under selection takes to J13 cell.

how to calculate delay fine

delay fine

resulting Total Electric bill

For net bill calculation you have to sum Net Bill and Delay fine

=J8+K8

OR

=sum(J8:K8)

total electric bill

Now this is the complete total electric bill.

Learn More…

Subscribe Me To Get Newsletters...