Average kWh based on month and time

K

karkas

Board Regular
Joined
Oct 17, 2007
Messages
170
Office Version
  1. 2013
Platform
  1. Windows
Here is a small sample below. I have about 12,000 rows of energy usage data by the hour per day.

I would like to know what my average kWh usage is at a specific hour per month. I think I need to use some kind of index/match sum function, but I'm not really sure how to do it. I also played with some pivot charts, but I couldnt get that to return anything worthwhile.

Column A is the date, column B is the hour, Column C is my off-peak usage, and column D is on peak usage.


day hour offpeak kWh onpeak kWh
8/11/2017 11:00 PM 6.69
8/11/2017 10:00 PM 8.65
8/11/2017 9:00 PM 6.82
8/11/2017 8:00 PM 4.61
8/11/2017 7:00 PM 4.62
8/11/2017 6:00 PM 5.1
8/11/2017 5:00 PM 6.35
8/11/2017 4:00 PM 4.78
8/11/2017 3:00 PM 3.51
8/11/2017 2:00 PM 3.93
8/11/2017 1:00 PM 2.85
8/11/2017 12:00 PM 1.96
8/11/2017 11:00 AM 3.54
8/11/2017 10:00 AM 4.2
8/11/2017 9:00 AM 2.69
8/11/2017 8:00 AM 3.97
8/11/2017 7:00 AM 3.05
8/11/2017 6:00 AM 2.33
8/11/2017 5:00 AM 3.39
8/11/2017 4:00 AM 5.21
8/11/2017 3:00 AM 5.14
8/11/2017 2:00 AM 5.45
8/11/2017 1:00 AM 6.32
8/11/2017 12:00 AM 3.55

Thanks in advance!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Something like this?
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Drag formula down as needed.
Excel Workbook
ABCDEF
1DayHourKwh
28/11/201711:00 PM6.69Month8
38/11/201710:00 PM8.65Hour
48/11/20179:00 PM6.8211:00 PM5.115
58/11/20178:00 PM4.6110:00 PM6.425
68/11/20177:00 PM4.629:00 PM4.755
78/11/20176:00 PM5.18:00 PM4.29
88/11/20175:00 PM6.357:00 PM3.835
98/11/20174:00 PM4.786:00 PM3.715
108/11/20173:00 PM3.515:00 PM4.87
118/11/20172:00 PM3.934:00 PM4.995
128/11/20171:00 PM2.853:00 PM4.325
138/11/201712:00 PM1.962:00 PM4.69
148/11/201711:00 AM3.541:00 PM2.95
158/11/201710:00 AM4.212:00 PM1.96
168/11/20179:00 AM2.6911:00 AM3.54
178/11/20178:00 AM3.9710:00 AM4.2
188/11/20177:00 AM3.059:00 AM2.69
198/11/20176:00 AM2.338:00 AM3.97
208/11/20175:00 AM3.397:00 AM3.05
218/11/20174:00 AM5.216:00 AM2.33
228/11/20173:00 AM5.145:00 AM3.39
238/11/20172:00 AM5.454:00 AM5.21
248/11/20171:00 AM6.323:00 AM5.14
258/11/201712:00 PM3.552:00 AM5.45
268/12/201711:00 PM3.541:00 AM6.32
278/12/201710:00 PM4.2
288/12/20179:00 PM2.69
298/12/20178:00 PM3.97
308/12/20177:00 PM3.05
318/12/20176:00 PM2.33
328/12/20175:00 PM3.39
338/12/20174:00 PM5.21
348/12/20173:00 PM5.14
358/12/20172:00 PM5.45
368/12/20171:00 PM3.05
Sheet
 
Upvote 0
Thank you very much!

I have to combine the off-peak/on-peak columns to get your formula to work, but I can manage that.

I did recognize a flaw in my plan after the application... I need to separate the times out for mon-fri as the weekend times of 12:00-18:00 charge as off peak instead of on-peak.

I can probably massage the data to get it to work.

Thanks again!
 
Upvote 0
You're welcome.
You could add the WEEKDAY function to the formula to break the weekends and Mon-Fri. You would only use the formula for the weekend times at the off peak rate.
Excel Workbook
ABCDEFG
1DayHourKwh
28/11/201711:00 PM6.69Month8
38/11/201710:00 PM8.65HourWeekdayWeekend
48/11/20179:00 PM6.8211:00 PM6.693.54
58/11/20178:00 PM4.6110:00 PM8.654.2
68/11/20177:00 PM4.629:00 PM6.822.69
78/11/20176:00 PM5.18:00 PM4.613.97
88/11/20175:00 PM6.357:00 PM4.623.05
98/11/20174:00 PM4.786:00 PM5.12.33
108/11/20173:00 PM3.515:00 PM6.353.39
118/11/20172:00 PM3.934:00 PM4.785.21
128/11/20171:00 PM2.853:00 PM3.515.14
138/11/201712:00 PM1.962:00 PM3.935.45
148/11/201711:00 AM3.541:00 PM2.853.05
158/11/201710:00 AM4.212:00 PM2.755
168/11/20179:00 AM2.6911:00 AM3.54
178/11/20178:00 AM3.9710:00 AM4.2
188/11/20177:00 AM3.059:00 AM2.69
198/11/20176:00 AM2.338:00 AM3.97
208/11/20175:00 AM3.397:00 AM3.05
218/11/20174:00 AM5.216:00 AM2.33
228/11/20173:00 AM5.145:00 AM3.39
238/11/20172:00 AM5.454:00 AM5.21
248/11/20171:00 AM6.323:00 AM5.14
258/11/201712:00 PM3.552:00 AM5.45
268/12/201711:00 PM3.541:00 AM6.32
278/12/201710:00 PM4.2
288/12/20179:00 PM2.69
298/12/20178:00 PM3.97
308/12/20177:00 PM3.05
318/12/20176:00 PM2.33
328/12/20175:00 PM3.39
338/12/20174:00 PM5.21
348/12/20173:00 PM5.14
358/12/20172:00 PM5.45
368/12/20171:00 PM3.05
Sheet
 
Upvote 0
Perfect!

I had already just created a second grid for the second column, but your solultion is much more elegant.

I will probably have to redo this data soon and I'll use yours for that one.

Thanks again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
388
freeb1893
F
H
  • Question Question
Having trouble calendarizing a utility bill across multiple months. Is there a quicker method than what I've done so far?
Replies
1
Views
613
helnuma2k
H
H
  • Question Question
Difficulty transposing and then stacking time series data
Replies
5
Views
734
Hawk11ns
H
B
  • Question Question
Calculating an average cycle time
Replies
3
Views
1K
SpillerBD
S
C
  • Question Question
Determining peak/off-peak durations between time stamps with varied peak hour ranges
Replies
3
Views
4K
Fazza
F

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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.
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.
Go back
Back
Top