Calculating a bill using a graduated rate...
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? |
=IF(A1<=600,A1*0.06,36+(A1-600)*0.08)
-- Vasant "Dave Smith" <Dave wrote in message ... 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? |
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 1200 costs .30 per kwH over 1200 "Vasant Nanavati" wrote: =IF(A1<=600,A1*0.06,36+(A1-600)*0.08) -- Vasant "Dave Smith" <Dave wrote in message ... 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? |
Another possibility is =A1*0.06+(A1600)*(A1-600)*0.02
best wishes-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dave Smith" <Dave wrote in message ... 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? |
Dave
Have a look at JE McGimpsey's variable rate formula(s). http://www.mcgimpsey.com/excel/variablerate.html Gord Dibben Excel MVP On Sun, 22 May 2005 10:51:04 -0700, "Dave Smith" wrote: 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 1200 costs .30 per kwH over 1200 "Vasant Nanavati" wrote: =IF(A1<=600,A1*0.06,36+(A1-600)*0.08) -- Vasant "Dave Smith" <Dave wrote in message ... 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? |
How about
=A1*0.06+(A1600)*(A1-600)*0.02+(A1800)*(A1-800)*0.04+(A11000)*(A1-10000)*0.05+(A11200)*(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! -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dave Smith" wrote in message ... 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 1200 costs .30 per kwH over 1200 "Vasant Nanavati" wrote: =IF(A1<=600,A1*0.06,36+(A1-600)*0.08) -- Vasant "Dave Smith" <Dave wrote in message ... 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? |
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! In article , "Dave Smith" wrote: 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 1200 costs .30 per kwH over 1200 |
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 |
All times are GMT +1. The time now is 12:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com