The dataset shown below contains the current meter reading, the past meter reading, and the consumed units of electricity of ten different households located inside a building. The cells on the right overview the final output you are going to get.
Consider the following dataset “Monthly electricity usage of Johnson H. housing society (Building 1)”.
Method 1 – Electricity Bill Calculation for Fixed Unit Price
Steps:
- Add the Unit Price and Total Bill columns (G and H) to the right.
- Fill in the column G with desired values.
- Go to cell H5 and type the following formula:
=F5*G5
- Press ENTER to get the result.
- Place the cursor at the bottom right corner of cell H5, left-click on it and drag the cursor down to cell H14.
Method 2 – Electricity Bill Formula for Variable Unit Price (Slab)
Consider a table such as the one below, showing progressive prices in the electricity usage slabs.
Steps:
- Make the column G as Total Bill.
- Select cell G5 and insert the following formula and press ENTER to get the expected result:
=IF(F5<=400,F5*0.1,IF(F5<=700,(400*0.1+(F5-400)*0.2),IF(F5<=1000,(400*0.1+(700-400)*0.2+(F5-700)*0.3),IF(F5<=1300,(400*0.1+(700-400)*0.2+(1000-700)*0.3+(F5-1000)*0.4),IF(F5<=1600,(400*0.1+(700-400)*0.2+(1000-700)*0.3+(1300-1000)*0.4+(F5-1300)*0.5),0)))))Formula Breakdown:
- =IF(F5<=400,F5*0.1, … ): This is the first condition. It checks if the value in cell F5 is less than or equal to 400. If it is, it returns the result of multiplying F5 by 0.1 (10% of F5). If F5 is greater than 400, it proceeds to the next condition.
- IF(F5<=700, … , … ): This is the second condition within the first condition. It checks if F5 is less than or equal to 700. If it is, it calculates a new result: 10% of the first 400 units plus 20% of the amount over 400 units. The expression (400*0.1 + (F5-400)*0.2) is used for this calculation. If F5 is greater than 700, it moves on to the next condition.
- IF(F5<=1000, … , … ): This is the third condition within the first condition. It checks if F5 is less than or equal to 1000. If it is, it calculates a new result: 10% of the first 400 units plus 20% of the next 300 units (400 to 700) plus 30% of the amount over 700 units. The expression (400*0.1 + (700-400)*0.2 + (F5-700)*0.3) is used for this calculation. If F5 is greater than 1000, it proceeds to the next condition.
- IF(F5<=1300, … , … ): This is the fourth condition within the first condition. It checks if F5 is less than or equal to 1300. If it is, it calculates a new result: 10% of the first 400 units plus 20% of the next 300 units plus 30% of the next 300 units (700 to 1000) plus 40% of the amount over 1000 units. The expression (400*0.1 + (700-400)*0.2 + (1000-700)*0.3 + (F5-1000)*0.4) is used for this calculation. If F5 is greater than 1300, it moves on to the next condition.
- IF(F5<=1600, … , 0): This is the fifth and final condition within the first condition. It checks if F5 is less than or equal to 1600. If it is, it calculates a new result: 10% of the first 400 units plus 20% of the next 300 units plus 30% of the next 300 units plus 40% of the next 300 units (1000 to 1300) plus 50% of the amount over 1300 units. The expression (400*0.1 + (700-400)*0.2 + (1000-700)*0.3 + (1300-1000)*0.4 + (F5-1300)*0.5) is used for this calculation. If F5 is greater than 1600, it returns 0.
- Drag the Fill Handle down to fill in the rest of the cells in the column.
You can simplify the formula by manually calculating some of the constant values. Additionally, you can use dynamic information based on the table and use cell references and more advanced formulas to make it more efficient, allowing you to change the prices and automatically update the results.
Read More: Invoice Excel Formula
Things to Remember
- Careful use of parenthesis is very important while using multiple nested IF functions.
Download Practice Workbook
You can download and practice the dataset we used to prepare this article.
Related Articles
- How to Create Bill Payment Checklist in Excel
- Create Fully Automatic Invoice in Excel
- How to Create Bill Book in Excel
- Create Invoice in Word from Excel Data
- How to Create Proforma Invoice in Excel
- Labour Contractor Bill Format in Excel
- Hotel Bill Format in Excel
Electricity Bill Formula for Variable Unit Price (Slab) Is totally wrong.
Hi MAHMUD,
Thank you for your valuable suggestion. I updated the formula for calculating electricity bill with Variable Unit Price (Slab). Hope you find this article useful.
Regards,
ExcelDemy