Instructions for calculating the living electricity bill with Excel

Instructions for calculating the living electricity bill with Excel


According to the announcement from the Ministry of Industry and Trade from December 1, 2017, the electricity retail price has changed over the previous years, the average increase with the new price is 1,720.65 VND / kWh, an increase of 6.08% compared to the previous years. With the average electricity price in the past, please see the instructions for calculating electricity bills in Excel to take the initiative in calculating your electricity bills, especially those of your business / dealer of electricity.


In this article, I will show you how to make a table to calculate electricity costs in Excel and mainly use the functions available in Excel to calculate, specifically here using the IF function.

INSTRUCTIONS FOR CHARGING ELECTRICITY LIVING BY EXCEL

First, you need to set up a bill of electricity, for living expenses will be calculated according to the following ladder:

– 1-50kw: 1549đ: Electricity consumption in the first 50 numbers from 1-50kw will cost 1549 VND / each number.
– 51-100: 1600đ: Power consumption of the next 50 numbers from 51-100kw will cost 1600 VND / each number
– 101-150: 1858đ: The electricity consumption of the next 50 numbers from 101-150kw will cost 1858 VND / each number
– 151-200: 2340đ: The electricity consumption of the next 50 numbers from 151-200kw will cost 2340 VND / each number
– 201-300: 2615đ: Consumption of the next 100 numbers from 201-300kw will cost 2615 VND / each number
– 301-400: 2701đ: Consumption of the next 100 numbers from 301-400kw will cost 2701 VND / each number
– 401 or more: 2271đ: From the 401st phone number, the price is 2271 VND / each phone number

The price does not include 10% VAT.

We have a given data table as shown below and need to create a formula to calculate the electricity bill:

Guide to excel in excel 2

Step 1: First you need to calculate the amount of electricity consumed by subtracting the old number from the new one.

You enter in the box F4 = E4-D4 then press Enter and scroll down to complete the boxes below

Guide to excel in state operations 3

Step 2: You start calculating the electricity consumption according to each price level. Here, you need to understand and interpret these levels to apply the IF function in Excel to calculate.

* Level 1-50kw: 1549đ

Enter in the box G4 recipe: = IF (F450,50))= 50, f4, if (f4>.If the consumption is less than or equal to 50, the result will be the cell F4 itself, the bigger will be equal to 50.

guide to excel state of excel 4S

* Level 51-100kW: 1600đ

Enter in the box H4 recipe: = IF (F450, F4 = 100.50)))= 100), f4-50, if (f4> 51,0, if (and (f4>. If the power consumption is less than 51, the result is 0. If the power consumption is greater than 50 and less than or equal to 100, the result is the power consumption minus 50. If the power consumption is greater or equals 100, the result returns 50.

guide to excel state of excel 5

* Level 101-150kW: 1858đ

Enter in the box I4 recipe: = IF (F4100, F4100,50))). = 150), f4-100, if (f4> 101,0, if (and (f4>If the power consumption is less than 101, the result is zero. If the power consumption is greater than 100 and less than or equal to 150, the result is the power consumption minus 100. If the power consumption is greater than 100 , the result returns 50.

* Level 151-200kW: 2340đ

Enter in the box J4 recipe: = IF (F4 = 150, F4 = 200.50)))= 200), f4-150, if (f4> 151,0, if (and (f4>. If the power consumption is less than 151, the result is 0. If the power consumption is greater than or equal to 150 and less than or equal to 200, the result is the power consumption minus 150. If the power consumption is large more than or equal to 200, the result returns 50.

* Level 201-300kw: 2615đ

Enter in the box K4 recipe: = IF (F4 = 201, F4 = 300,100))= 300), f4-200, if (f4> 201,0, if (and (f4>. If the power consumption is less than 201, the result is 0. If the power consumption is greater than or equal to 201 and less than or equal to 300, the result is the power consumption minus 200. If the power consumption is large more than or equal to 300, the result returns 100.

* Level 301-400kW: 2701 VND

Enter in the box L4 recipe: = IF (F4 = 301, F4 = 400,100)))= 400), f4-300, if (f4> 301,0, if (and (f4>. If the power consumption is less than 301, the result is 0. If the power consumption is greater than or equal to 301 and less than or equal to 400, the result is the power consumption minus 300. If the power consumption is large more than 400, the result returns 100.

* Level 401kW or More: 2271đ

Enter in the box M4 recipe: = IF (F4400, F4-400))401,0, if (f4>. If the power consumption is less than 401, the result is 0. If the power consumption is greater than 400, the result will be the power consumption minus 400.

Step 3: You will calculate the Total amount payable by the customer, the Total Amount will be equal to the electricity consumption multiplied by its corresponding Unit Price.

Enter the cell N4 formula: = G4 * G3 + H4 * H3 + I4 * I3 + J4 * J3 + K4 * K3 + L4 * L3 + M4*M3 (G3, H3, I3, J3, K3, L3, M3 correspond to Unit price of: VND 1549, VND 1600, VND 1858, VND 2340, VND 2615, VND 2701, VND 2271)

guide to excel state of excel 6

As a result, you have calculated the electricity and the amount that customers have to pay. From here you can copy the formulas for the rows below to work out for all customers.

guide to excel in state 7 excel

So I have just guided you How to calculate electricity costs for living using Excel. Through this tutorial, you will certainly find it easier to practice calculating electricity charges in Excel, you can also refer to how to Billing electric bill in Excel here.

https://thuthuat.taimienphi.vn/huong-dan-tinh-tien-dien-sinh-hoat-bang-excel-42446n.aspx
Also if you need to learn more about the IF function in Excel, please refer to the usage guide IF function in Excel that Taimienphi.vn shared. Good luck!

.

Related Posts

Leave a Reply

Your email address will not be published.