Using the IF function to quickly calculate electricity bills in Excel
Electricity bills are a cost that every household and business has to pay. But can accountants control this amount and not let it exceed their ability to pay? Use the IF function to calculate how much money you have to pay for electricity. Since the electricity unit price is calculated according to different levels of electricity consumption, we will need to calculate according to each level. In this article, Buffcom.net will share with you how to use the IF function to quickly calculate electricity bills in Excel.
Using the IF function to quickly calculate electricity bills in Excel
For example, we have a table of electricity consumption of 5 households as shown below. The Previous reading column is the electricity meter reading at the end of the previous month. The Current reading column is the electricity meter reading at the end of this month.
In the table below is the electricity prices for each level of electricity consumption:
- Level 1: For kWh from 0-50: 1,678 VND/kWh
- Level 2: For kWh from 51-100: 1,734 VND/kWh
- Level 3: For kWh from 101-200: 2,014 VND/kWh
- Level 4: For kWh from 201-300: 2,536 VND/kWh
- Level 5: For kWh from 301-400: 2,834 VND/kWh
- Level 6: For kWh from 401 and above: 2,927 VND/kWh
Using the IF function to quickly calculate electricity bills in Excel
First, we need to calculate the amount of electricity consumed by each household in the period by subtracting the Previous reading from the Current reading. In cell D2, we have the following formula: =C2-B2
Copy the formula for the remaining cells, we will get the result of calculating the amount of electricity consumed by each household in the month.
Using the IF function to quickly calculate electricity bills in Excel
Next, to calculate the electricity bill according to the progressive table for each level of electricity consumption, we apply the IF function to the formula in cell E2 as follows:
=IF(D2>400;(D2-400)*2927+909000;IF(D2>300;(D2-300)*2834+625600;IF(D2>200;(D2-200)*2536+372000;IF(D2>100;(D2-100)*2014+ 170600;IF(D2>50;(D2-50)1734+83900;D21678)))))
Copy the above formula for the remaining cells, we will calculate the electricity bill without including value-added tax for each household.
Using the IF function to quickly calculate electricity bills in Excel
Next, to calculate the electricity bill including VAT, we multiply the electricity bill before tax by 1.1. We have the formula in cell F2 as follows: =E2*1.1
Copy the formula for the remaining cells in the column, we will get the electricity bill including VAT for each household.
Using the IF function to quickly calculate electricity bills in Excel
Thus, the above article has guided you on how to use the IF function to quickly calculate electricity bills in Excel. Hopefully, the article will be useful for you in the work process. Wish you success!
