Calculating a bill using a graduated rate...


G

Guest

I'm trying to calculate an electricity bill which uses different values based
on the amount used. For the first 600 kwh, the charge is .06/kwh. For
anything above 600 kwh, the charge is .08/kwh. So if my usage is 700 kwh, my
bill is $36+$8. Any ideas on which formula I should use for this?
 
Ad

Advertisements

G

Guest

Thanks Vasant.

Now what if the rate calculation gets more complicated? Is there some way
to use a lookup table? For example:

0-600 costs .06 per kwH
601-800 costs .08 per kwH over 600
801-1000 costs .10 per kwH over 800
1001-1200 costs .15 per kwH over 1000
 
B

Bernard Liengme

How about
=A1*0.06+(A1>600)*(A1-600)*0.02+(A1>800)*(A1-800)*0.04+(A1>1000)*(A1-10000)*0.05+(A1>1200)*(A1-1200)*0.15
OR
=MIN(A1,600)*0.06+MAX(0,MIN(200,A1-600))*0.08+MAX(0,MIN(200,A1-800))*0.1+MAX(0,MIN(200,A1-1000))*0.15+MAX(0,A1-1200)*0.3

To quote my Irish math teacher from long ago:there are more ways of killing
a pig than stuffing it with butter!
 
Ad

Advertisements

D

Domenic

As suggested by Gord Dibben, using JE McGimpsey's variable rate
formula...

=SUMPRODUCT(--(A1>{0,600,800,1000,1200}),A1-{0,600,800,1000,1200},{0.06,0
..02,0.02,0.05,0.15})

Hope this helps!
 
Ad

Advertisements

R

Ron Rosenfeld

Now what if the rate calculation gets more complicated? Is there some way
to use a lookup table? For example:

0-600 costs .06 per kwH
601-800 costs .08 per kwH over 600
801-1000 costs .10 per kwH over 800
1001-1200 costs .15 per kwH over 1000
Set up a rate table somewhere on your worksheet.


kWh Base Rate
0 $ 0.00 $0.06
600 $36.00 $0.08
800 $52.00 $0.10
1000 $72.00 $0.15
1200 $102.00 $0.30


The kWh and Rate columns are what you have given.

The Base rate is computed using a formula of the type:

If the table is in J1:L5 then:

K3: =(J3-J2)*L2+K2

and copy/drag down.

Then, with the usage in A2, use the following formula to compute the bill:

=VLOOKUP(A2,RateTable,2)+
(A2-VLOOKUP(A2,RateTable,1))*
VLOOKUP(A2,RateTable,3)




--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top