Md. Nafis Soumik
We all need to use electricity to conduct our day-to-day activities in our everyday life. So, the electric bill may become a very significant expense to be included in our monthly budget calculation. This article demonstrates how to use the electricity bill calculation formula in Excel in 2 suitable ways.
We shall use a sample dataset as an example to show. The information below shows the current meter reading, the past meter reading, and the consumed units of electricity of ten different households located inside a building.
Before starting the article, let’s have an overview of the final output you are going to get.
Download Practice Workbook
You can download and practice the dataset we used to prepare this article.
General Formula to Calculate Electric Energy Usage and Total Bill
The electricity bill can be calculated manually with the help of a theoretical scientific formula. Which is:
Here,
Kilowatt = Unit of Power
Kilowatt-hour = Special unit of Energy to calculate the electric energy usage and subsequently the cost of it. (We can calculate an appliance’s KW-h by looking at its power rating and then multiplying it by 3600)
Let’s assume, a typical space heater has a power rating of 1 KW. So, the energy consumed by it in one hour is 1 x 3600 = 3600 KW-h. Assuming, it remains active for 1 hour a day on average, during one month period it remains active for a total of 1 x 30 = 30 hours. And if the current per unit cost of electricity is $1, the electricity bill that would have to be paid for using this appliance during one month period would be 3600 x 30 = $108000.
2 Cases to Perform Electricity Bill Calculation Formula in Excel
Let’s assume we have a dataset, namely “Monthly electricity usage of Johnson H. housing society (Building 1)”. You can use any dataset suitable for you.
Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.
1. Electricity Bill Calculation for Fixed Unit Price
In this section, we are going to show you how to calculate your monthly electricity bill when the unit price is fixed. Here, we only need to use the multiplication operator. No additional functions are needed.
Steps:
- Firstly, go to cell H5 and type the following formula:
=F5*G5
- Next, press ENTER to get the result.
- Finally, place the cursor at the bottom right corner of cell H5, left-click on it and drag the cursor down to cell H14. You will get the calculated result for the rest of the flats too.
Similar Readings
- Tally Bill Format in Excel (Create with 7 Easy Steps)
- Excel Invoice Tracker (Format and Usage)
- Transport Bill Format in Excel (Create in 4 Simple Steps)
- How to Create a Cash Bill Format in Excel (A step-by-step Guideline)
- Tax Invoice Format in Excel (Download the Free Template)
2. Electricity Bill Formula for Variable Unit Price (Slab)
At this point, you are going to learn to carry out the same task in the case of variable unit prices. It means the unit price would be different for different ranges of electricity usage or slabs. Also here, you will only need to use the multiplication operator and the function named IF.
We have provided a table below, in which different unit prices according to the different electricity usage slabs are mentioned.
Steps:
- First of all, move to cell G5 and insert the following formula:
=IF(F5<=400,F5*0.1,IF(F5<=700,F5*0.2,IF(F5<=1000,F5*0.3,IF(F5<=1300,F5*0.4,IF(F5<=1600,F5*0.5,0)))))Formula Breakdown:
- “IF(F5<=400,F5*0.1,” this part means if the value in F5 is less than or equal to 400, the function would take the value in F5, multiply It by 0.1 and then return the result in G5, if not, the function would proceed to the next part.
- “IF(F5<=700,F5*0.2,” this part means if the value in F5 is less than or equal to 700, the function would take the value in F5, multiply It by 0.2 and then return the result in G5, if not, the function would proceed to the next part.
- “IF(F5<=1000,F5*0.3,” this part means if the value in F5 is less than or equal to 1000, the function would take the value in F5, multiply It by 0.3 and then return the result in G5, if not, the function would proceed to the next part.
- “IF(F5<=1300,F5*0.4,” this part means if the value in F5 is less than or equal to 1300, the function would take the value in F5, multiply It by 0.4 and then return the result in G5, if not, the function would proceed to the next part.
- “IF(F5<=1600,F5*0.5,0)))))” this part means if the value in F5 is less than or equal to 1600, the function would take the value in F5, multiply It by 0.5 and then return the result in G5, otherwise the function would return the value “0” in G5.
- Afterward, press ENTER to get the expected result.
- Lastly, repeat the task of step 3 from method 1 to obtain the rest of the results.
Read More: Invoice Excel Formula
Things to Remember
- Nowadays, almost all users are charged with variable unit prices to reduce the extent of electrical energy usage by encouraging the users to use electricity as little as possible.
- Careful use of parenthesis is very important while you are using multiple nested IF functions.
Conclusion
You may now know to use formulas for your electricity bill calculation in Excel using both fixed and variable unit prices. Use these anytime you need to, and let us know if you think of any other, superior methods. If you have any questions, feel free to leave a remark in the space below.
Related Articles
- How to Create Bill Payment Checklist in Excel (2 Smart Ways)
- Create Fully Automatic Invoice in Excel (with Easy Steps)
- How to Create Bill Book in Excel (2 Easy Ways)
- Create Invoice in Word from Excel Data (with Easy Steps)
- How to Create Proforma Invoice in Excel (Download Free Template)
- Labour Contractor Bill Format in Excel (Download Free Template)
- Hotel Bill Format in Excel (Create with Easy Steps)
