# Electricity Billing Calculation

#### sooshil

##### Board Regular
I wanted to use the easiest formula for to calculate the total amount in the following excel sheet. The conditions required for the calculation is in the another table below. Please help me to find the easiest formula.

 ID Customer Name Customer Type Previous Reading Current Reading Unit Consumed Total Amount 1 Angelina Domestic 1258 1320 62 2 Boyd Domestic 1485 1698 213 3 Steven Agriculture 1103 1170 67 4 Joe Industry 2566 2891 325 5 John Agriculture 1654 1841 187

<tbody>
</tbody>

Conditons:

 Customer Type Minimum Charge Upto 20 units For Consumed Unit between 21-250, additional cost per unit For Consumed Unit above 250, additional cost per unit Domestic \$ 80.00 \$ 7.30 \$ 11.25 Industry \$ 200.00 \$ 10.15 \$ 15.5 Agriculture \$ 50.00 \$ 5.40 \$ 7

<tbody>
</tbody>

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Do a Google for tiered pricing.
Looks like SUMPRODUCT may yield a solution but I haven't tried this sort of thing before.

Do a Google for tiered pricing.
Looks like SUMPRODUCT may yield a solution but I haven't tried this sort of thing before.

I couldn't get anything after various search.
Would you please get into my problem and suggest the solution?

Thanks.

Customer data in A to F . F =units used. formula in G . tarrif table in M to O

=VLOOKUP(C2,\$M\$2:\$O\$5,2,FALSE)+IF(\$F2<=20,0,VLOOKUP(\$C2,\$M\$2:\$O\$5,3,FALSE)*MIN(\$F2-20,250-20))+IF(\$F2<=250,0,(\$F2-250)*(VLOOKUP(\$C2,\$M\$2:\$P\$5,4,FALSE)))

but to make it easier to follow suggest you have 4 columns

G =VLOOKUP(C2,\$M\$2:\$O\$5,2,FALSE) this is in charge \$

H =IF(\$F2<=20,0,VLOOKUP(\$C2,\$M\$2:\$O\$5,3,FALSE)*MIN(\$F2-20,250-20)) 2nd tier charges

I =IF(\$F2<=20,0,VLOOKUP(\$C2,\$M\$2:\$O\$5,3,FALSE)*MIN(\$F2-20,250-20)) top level charges

then ad together in the last column

Last edited:
Customer data in A to F . F =units used. formula in G . tarrif table in M to O

=VLOOKUP(C2,\$M\$2:\$O\$5,2,FALSE)+IF(\$F2<=20,0,VLOOKUP(\$C2,\$M\$2:\$O\$5,3,FALSE)*MIN(\$F2-20,250-20))+IF(\$F2<=250,0,(\$F2-250)*(VLOOKUP(\$C2,\$M\$2:\$P\$5,4,FALSE)))

Why M to O only

That tariffs table takes the range M1:P4. isn't it?

Putting the main table in A1:G6 and putting the conditions in A10:D13 this works placed in G2 and copied down.

=VLOOKUP(C2,\$A\$10:\$D\$13,2,0)+IF(F2>250,((F2-250)*VLOOKUP(C2,\$A\$10:\$D\$13,4,0)),0)+IF(F2>250,230*VLOOKUP(C2,\$A\$10:\$D\$13,3,0),(F2-20)*VLOOKUP(C2,\$A\$10:\$D\$13,3,0))

=IF(F2>250,(20*VLOOKUP(C2,\$A\$12:\$D\$14,2,0))+(230*VLOOKUP(C2,\$A\$12:\$D\$14,3,0))+((F2-250)*VLOOKUP(C2,\$A\$12:\$D\$14,4,0)),IF(F2>20,(20*VLOOKUP(C2,\$A\$12:\$D\$14,2,0))+((F2-20)*VLOOKUP(C2,\$A\$12:\$D\$14,3,0)),F2*VLOOKUP(C2,\$A\$12:\$D\$14,2,0)))
 "\$A\$12:\$D\$14" is the array for the price structure
<colgroup><col width="820" style="width: 615pt; mso-width-source: userset; mso-width-alt: 29988;"> <tbody> </tbody>

Putting the main table in A1:G6 and putting the conditions in A10:D13 this works placed in G2 and copied down.

=VLOOKUP(C2,\$A\$10:\$D\$13,2,0)+IF(F2>250,((F2-250)*VLOOKUP(C2,\$A\$10:\$D\$13,4,0)),0)+IF(F2>250,230*VLOOKUP(C2,\$A\$10:\$D\$13,3,0),(F2-20)*VLOOKUP(C2,\$A\$10:\$D\$13,3,0))

Dear Steve

Your formula is unable to maintain the minimum cost. i.e. If a domestic user consumes less than 20 units, then the charge must be Rs. 80 as this is the minimum cost.

This is not working in your formula. Other I am checking. Any way thanks.

=IF(F2>250,(20*VLOOKUP(C2,\$A\$12:\$D\$14,2,0))+(230*VLOOKUP(C2,\$A\$12:\$D\$14,3,0))+((F2-250)*VLOOKUP(C2,\$A\$12:\$D\$14,4,0)),IF(F2>20,(20*VLOOKUP(C2,\$A\$12:\$D\$14,2,0))+((F2-20)*VLOOKUP(C2,\$A\$12:\$D\$14,3,0)),F2*VLOOKUP(C2,\$A\$12:\$D\$14,2,0)))
 "\$A\$12:\$D\$14" is the array for the price structure

<tbody>
</tbody>

Dear Vogel

For 19 units consumed by a domestic user, it gives the bill of 1520. Which is not what we need. Any way thank you very much for your concern. Please kindly suggest the correction(s) if any.

Replies
3
Views
1K
Replies
3
Views
2K

1,219,958
Messages
6,151,147
Members
451,011
Latest member
Pigdog89

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back