I am trying to veryify my power bill and have raw meter readings showing kwh consumed in every half hour interval
Is there a formula I can use to calculate the price I should be charged per half hour block?
I am on time of use so i get charged as follows
between 7am and 1pm mon - fri - shoulder rate (0.31/kwh)
between 1pm and 8pm mon - fri - peak rate (0.41/kwh)
all other times (all day weekends) - off peak (0.17/kwh)
I think this is what you're after. I created sample data for testing purposes.
The Begin and End columns are formatted as Excel Dates and Times. The Begin column is formatted as m/dd/yy h:mm AM/PM and the End column as m/dd/yy h:mm:ss AM/PM. Notice that the End column is superfluous anyway. The Weekday column is fyi; it is simply =A8 and is formatted DDDD.
Copy the formulas downwards.
Book1
A
B
C
D
E
F
1
0
0.17
2
between 7am and 1pm mon - fri - shoulder
rate (0.31/kwh)
6:59:59 AM
0.31
3
between 1pm and 8pm mon - fri - peak
rate (0.41/kwh)
12:59:59 PM
0.41
4
all other times (all day weekends) - off
peak (0.17/kwh)
Thanks alot for this.. it works perfectly
Just wondering if it would be possible to alter the formula to add one more tarrif.. between 8pm and 10pm weekdays it would be should rate 0.31.. I have modified the table below
A
B
C
D
E
F
1
0
0.17
2
between 7am and 1pm mon - fri - shoulder rate (0.31/kwh)
6:59:59 AM
0.31
3
between 1pm and 8pm mon - fri - peak rate (0.41/kwh)
12:59:59 PM
0.41
4
all other times (all day weekends) - off peak (0.17/kwh)
7:59:59 PM
0.17
5
between 8pm and 10pm mon - fri - shoulder rate (0.31/kwh)
7:59:59 PM
<tbody>
</tbody><thead>
</thead><tbody>
</tbody>
Thanks again.. you saved me hours and hours of doing this manually
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.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
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
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
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.