# Formula for kilowatt hours

#### alfonsoto1

##### Board Regular
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.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Jonmo1

##### MrExcel MVP
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

##### Active Member
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

##### Board Regular
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

##### Active Member
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

Last edited:

#### Jonmo1

##### MrExcel MVP
I see what I did wrong

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

#### cummingsea

##### Active Member
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

##### Board Regular
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

##### MrExcel MVP
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

##### MrExcel MVP
Another option:

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

Replies
0
Views
261

1,191,700
Messages
5,988,173
Members
440,135
Latest member
DCDavid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back