Formula for kilowatt hours

[alfonsoto1]

I am creating a comparison sheet for kilowatt hours used, in D29 I have the actual KWH used. What I need is a formula that will give me the cost of the 1st 400 KWH at $0.08570 per KWH in BV7, the 2nd 400 KWH at $0.12175 per KWH in BW7, and the cost of the remaining KWH at $0.14427 per KWH in BX7. Now be advised that some times the KWH may totol 653 for the month and sometimes 1950 for the month. Hope you can help. Alfonso.

 

[cummingsea]

With Jonmo1's formulas at 1795kwh I got the following results:
1st 400 = 34.28
2nd 400 = 48.70
3rd 995 = 143.55
total = 226.53

 

[erik.van.geit]

Hi,

this would be more flexible for the future
you can add rates and copy down the formula

   A     B       C  D        
28                  KWH      
29 limit rate       1795     
30 400   0,0857     34,28    
31 800   0,12175    48,7     
32 9999  0,14427    143,5487      
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
D30     =MIN($D$29,A30)*B30
D31:D37 =MAX((MIN($D$29,A31)-A30)*B31,0)
[Table-It] version 09 by Erik Van Geit

I wonder if you are interested in the intermediate multiplications. Are you not after the result itself? Seems to me like this can be done in one formula.

kind regards,
Erik

 

[alfonsoto1]

Barry, they work fine. Thanks. Alfonso

 

[erik.van.geit]

found single formula

   A     B       C  D              
28 limit rate       KWH            
29 0     0          1795           
30 400   0,0857                    
31 800   0,12175    single formula 
32 9999  0,14427    226,5287       
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
D32     {=SUMPRODUCT((IF($D$29< A30:A32,$D$29,A30:A32)-A29:A31)*B30:B32)}
{=formula}:
 enter formula without {}
 confirm with Control-Shift-Enter
[Table-It] version 09 by Erik Van Geit

since Barry is watching I can put it here safely He'll correct or improve it if necessary

 

[alfonsoto1]

Thanks for all of your help, Everything is working fine now. You guys are great. Alfonso

 

[barry houdini]

found single formula

Hello Erik,

I don't think your formula works correctly for values of D29 less than 800.
Using your table you should be able to use a non-CSE SUMPRODUCT formula like

=SUMPRODUCT(--($D$29>A29:A31),$D$29-A29:A31,B30:B32-B29:B31)

which follows the method described here

....or another option

=D29*B32-MIN(A31,D29)*(B32-B31)-MIN(A30,D29)*(B31-B30)

 

[erik.van.geit]

Indeed, Barry, thanks!!