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.

 

[Jonmo1]

Try

1st 400 KWH at $0.08570 per KWH in BV7
=MIN(400,D29)*0.0857

2nd 400 KWH at $0.12175 per KWH in BW7
=IF(D29-400>0,D29-400,0)*0.12175

the remaining KWH at $0.14427 per KWH in BX7
=IF(D29-800>0,D29-800,0)*0.14427

Hope this helps..

 

[cummingsea]

jonmo1 I am not an expert at excel but I think somethig is wrong with the 2nd condition.
if you multiply 400 x 0.12175 = 48.70, but your formula is giving a result of 188.71.

 

[alfonsoto1]

The 1st formula works fine, however the 2nd and 3rd return a result of 0. there is 1795 KWH in D29 the 1st formula is $34.28 which is correct. Hope you can help. Alfonso

 

[cummingsea]

alfonsoto1 I tested the formulas that jonmo1 posted the 1st and 3rd worked fine the 2nd one gives the wrong amount.
1st = 34.28 correct
2nd = 188.71 incorrect Should be 48.70
3rd = 165.91 correct

 

[Jonmo1]

I see what I did wrong

2nd formula should be
=IF(D29-400>0,MIN(400,D29-400),0)*0.12175

 

[cummingsea]

jonmo1 that works, but alfonsoto1 said the third did not work for him, but I tested it and it worked fine for me.I tested it with 1950 kwh

 

[alfonsoto1]

The 2nd formula at 400 KWH should be 400*.12175= $48.70 and the third should be 995 KWH*.14427=$143.55. In D29 there is 1795 KWH.

 

[barry houdini]

You could use these 3 formulas

=MIN(400*0.0857,D29*0.0857)

=MEDIAN(0,400,D29-400)*0.12175

=MAX(0,D29-800)*0.14427

Note: if D29 is blank all 3 will display zero

 

[Oaktree]

Another option:

BV7=MIN(400,D29)*0.0857
BW7 =MAX(MIN(D29-400,400),0)*0.12175
BX7=MAX(0,D29-800)*0.14427