Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 22nd 05, 06:17 PM
Dave Smith
 
Posts: n/a
Default 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?

  #3   Report Post  
Old May 22nd 05, 06:51 PM
Dave Smith
 
Posts: n/a
Default

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?




  #5   Report Post  
Old May 22nd 05, 07:56 PM
Gord Dibben
 
Posts: n/a
Default

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?







  #6   Report Post  
Old May 22nd 05, 08:17 PM
Bernard Liengme
 
Posts: n/a
Default

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
news
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?






  #7   Report Post  
Old May 22nd 05, 08:32 PM
Domenic
 
Posts: n/a
Default

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

  #8   Report Post  
Old May 22nd 05, 10:06 PM
Ron Rosenfeld
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
subtraction of times, convert & multiply by a conditioned rate sanscull Excel Worksheet Functions 6 May 20th 05 02:02 AM
Calculating a rate for elapsed time? Keith Excel Discussion (Misc queries) 8 May 18th 05 09:14 PM
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) RICHARD Excel Worksheet Functions 1 March 15th 05 05:49 PM
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! RICHARD Excel Discussion (Misc queries) 0 March 1st 05 01:53 PM
Calculating Interest where rate changes per quarter Dean Strudwick Excel Discussion (Misc queries) 1 December 6th 04 11:36 PM


All times are GMT +1. The time now is 10:42 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017