Conditionally insert rows, sum and average

I guess I will be having a hard time explaining it so I will just try to attach a couple of excel files to illustrate it.

And this is the macro code I am using:

Please Login or Register  to view this content.

The bottom line is - it seems to work just fine, but when it gets to the 11 and 13 hours it gives wrong results and I can't figure out why.
I suppose that there could be an easier and most importantly - shorter way to achieve the same result which would minimise the chance of mistakes but I am pretty new in Excel VB, so I would appreciate it if you could help me in any way.

P.S. The rows for every time interval are not always the same number.

This is a lot of WB, Code to go through. Can you pinpoint where it goes wrong?

I would suggest using Formula's

Please Login or Register  to view this content.

I have marked them in red. But the point is - I am actually asking for another, shorter way to achieve this, so that it could minimise possible bugs, errors or mistakes. Because, personally, I can't see any difference in the code for 11 and 12 o'clock for example - and while one of them is working, the other one is not.

Basically, what should happen is - it should insert row before every one-hour interval, without the first one. Then it should sum the number of calls and the total time of the calls for each one-hour interval. Then it should calculate the average time per call for each time interval - in the next cell. (You could see that from the example xls sheets - it would be easier to understand it that way). And the last thing it should do - is to copy the data for the number of calls and the average time per call and paste it into two rows next to the table - the first one is the number of calls and the second one is the average time per call.

I hope that it is clearer now.

P.S. I just saw your suggestion... I would try it out. I think it could work. I would copy the information in the first sheet, then run macro to remove the unnecessary rows and columns (it works fine for the moment), and copy the information in the second sheet.
Just one 'dumb' question - would the formula still work if the data is not sorted?

Yes, while your code does not.

This is exactly why one must take care with VBA code. 99% of the time VBA code is build on some assuptions and you know what they say about assumptions...

Also, the first sheet - it should be something very similar to that but instead of showing the average time per call for each hour, it should show the average time per call per agent and also their average work ready time.
The problem is the number of agents is not always the same - there could be 2 agents at work, but there could also be 6 agents at work or even more if they decide to hire more people in the future.

I have attached a file, showing what the result should be like with 4 agents.

Is it also possible to be done with some kind of formula or with a macro??
Because I would have used the other macro and just type the name of every agent in the Find(What:="") VB function. But after these errors it's giving, I am reluctant to do so.

So I am open to any ideas

H7:

Please Login or Register  to view this content.

I7:

Please Login or Register  to view this content.