Home 
Search 
Today's Posts 
#1




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? 
#2




=IF(A1<=600,A1*0.06,36+(A1600)*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? 
#3




Thanks Vasant.
Now what if the rate calculation gets more complicated? Is there some way to use a lookup table? For example: 0600 costs .06 per kwH 601800 costs .08 per kwH over 600 8011000 costs .10 per kwH over 800 10011200 costs .15 per kwH over 1000 1200 costs .30 per kwH over 1200 "Vasant Nanavati" wrote: =IF(A1<=600,A1*0.06,36+(A1600)*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? 
#4




Another possibility is =A1*0.06+(A1600)*(A1600)*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? 
#5




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: 0600 costs .06 per kwH 601800 costs .08 per kwH over 600 8011000 costs .10 per kwH over 800 10011200 costs .15 per kwH over 1000 1200 costs .30 per kwH over 1200 "Vasant Nanavati" wrote: =IF(A1<=600,A1*0.06,36+(A1600)*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? 
#6




How about
=A1*0.06+(A1600)*(A1600)*0.02+(A1800)*(A1800)*0.04+(A11000)*(A110000)*0.05+(A11200)*(A11200)*0.15 OR =MIN(A1,600)*0.06+MAX(0,MIN(200,A1600))*0.08+MAX(0,MIN(200,A1800))*0.1+MAX(0,MIN(200,A11000))*0.15+MAX(0,A11200)*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 news Thanks Vasant. Now what if the rate calculation gets more complicated? Is there some way to use a lookup table? For example: 0600 costs .06 per kwH 601800 costs .08 per kwH over 600 8011000 costs .10 per kwH over 800 10011200 costs .15 per kwH over 1000 1200 costs .30 per kwH over 1200 "Vasant Nanavati" wrote: =IF(A1<=600,A1*0.06,36+(A1600)*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? 
#7




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: 0600 costs .06 per kwH 601800 costs .08 per kwH over 600 8011000 costs .10 per kwH over 800 10011200 costs .15 per kwH over 1000 1200 costs .30 per kwH over 1200 
#8




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: 0600 costs .06 per kwH 601800 costs .08 per kwH over 600 8011000 costs .10 per kwH over 800 10011200 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: =(J3J2)*L2+K2 and copy/drag down. Then, with the usage in A2, use the following formula to compute the bill: =VLOOKUP(A2,RateTable,2)+ (A2VLOOKUP(A2,RateTable,1))* VLOOKUP(A2,RateTable,3) ron 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
subtraction of times, convert & multiply by a conditioned rate  Excel Worksheet Functions  
Calculating a rate for elapsed time?  Excel Discussion (Misc queries)  
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets)  Excel Worksheet Functions  
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS!  Excel Discussion (Misc queries)  
Calculating Interest where rate changes per quarter  Excel Discussion (Misc queries) 