date time format problem with AVERAGEIF (test-values, condition, avg-values) formula

I have a data set with two columns, (A) date/time and (B) meter readings. It has over 15,000 entries (readings taken every 30mins by smart meter over a couple of years).

I'd like to generate a graph showing average hourly use over a 24hr cycle.

I'm trying to use AVERAGEIF (test-values, condition, avg-values) to obtain averages for half hourly intervals but keep running into DIV/0 and other errors:

tried adding a column using the HOUR formula to strip date values:

this works with a simple IF formula, displaying reading when valid and blank when not. I can then get the average for that half hour by highlighting the column. But I'd need to add 48 more columns to cover all the intervals. Seems long winded.

Instead, I thought using AVERAGEIF on the first 48 rows would give me what I needed. But I keep getting errors. One permutation/attempt shown below. I've tried dozens of different approaches but I think AVERAGEIF doesn't like mixing durations or any date/times with other values.

Any thoughts on a better approach?

many thanks,