Converting a Ping Log to Average Ping per Hour Chart

So I have an Excel Table that consists of data from a Ping utility (we're having a lot of connection issues on our DSL line), every 5-10 seconds the program pings various websites like google, yahoo, etc. and logs the ping time.

I'd like to make a chart or graph that shows the average ping per Hour (or 30 minutes, or whatever) for all these sites combined, basically take an hour of pings, find the average of all or just the pings from one IP, plot.

Table is
ColA = Date Time (8/20/2013 15:57)
ColB = IP Address (173.252.110.27)
ColC = Ping (123)
ColD = Result (Succeeded/Failed)
ColD = DNS (facebook.com, it's a vlookup from a table of IPs and their DNS address)

Any help is appreciated.
-Andy

Hi, have a look at the attached and see if this helps answer your question. it should guide you in the right direction. Look at sheet 1 & 2. Hope this helps. Just for future reference, try and upload a sample workbook as its difficult to understand the amount of data you are looking at, removing any sensitive data of course.

Originally Posted by interested

Hi, have a look at the attached and see if this helps answer your question. it should guide you in the right direction. Look at sheet 1 & 2. Hope this helps. Just for future reference, try and upload a sample workbook as its difficult to understand the amount of data you are looking at, removing any sensitive data of course.

Thanks for your response, this is good, but what if I have a lot more data, like 24 hours worth and several data points per minute?

I'd like to group the average pings per hour or half hour. Here's the table with a sample of data. I'll try to plug this into your example and see if I can figure it out. Thanks much.

lotta pings.xlsx

you can try this. On the pivot data, right click and choose the group function as in sample lotta pings. you can choose a lot of different options.

Thanks Interested, will try it out in an hour or so, gotta go do something else. thanks.

-A.

Thanks Interested, will try it out in an hour or so, gotta go do something else. thanks.

-A.

Originally Posted by interested

you can try this. On the pivot data, right click and choose the group function as in sample lotta pings. you can choose a lot of different options.

Yep, that's it, Group... (ie- Grouping), hot damn, group the time into seconds or minutes and you get the average ping per second/minute. Very nice. I knew it was something simple.

Great. If your problem is solved, please mark the thread as solved, and hit the star button to give some feedback!! Thanks