On Sun, 22 May 2005 10:51:04 -0700, "Dave Smith"
wrote:
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
1200 costs .30 per kwH over 1200
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
|