Electricity Billing Calculation

[sooshil]

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.

Thanks in advance.

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>

 

[Special-K99]

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

 

[sooshil]

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.

 

[liveinhope]

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

 

[Special-K99]

This looks related, I haven't looked closely at it.

Tiered Pricing Formula using SUMPRODUCT • ExcelKey.com - Free Excel Help

 

[sooshil]

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?

Please make it clear once.

 

[steve the fish]

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))

 

[vogel997]

=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>

 

[sooshil]

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.

 

[sooshil]

=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

Your formula calculates wrongly.
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.