Ping Test Spreadsheet/Grid

  • Thread starter Thread starter Dave Y
  • Start date Start date
D

Dave Y

Hello,

My employer recently ran some connectivity tests for our
network; there are 14 facilities and we ran 12 tests;
each test returned a result of 29 lines. I put the
results of the tests into an Excel spreadsheet which now
contains 4872 rows (14 * 12 * 29). From this spreadsheet
I need to create individual grids for each test that show
the results from each building (ie. if the ping result
is "Responding" or "Down"). So far I ran a small piece of
VBA that puts a value of either 1 or 0 in the last column
representing the result (1 = Responding; 0 = Down). I
need to either come up with formulas or VBA code (or
both) to enable me to create the grids. Here is a small
example of the spreadsheet:
BldgNum TestNum IP_Addr RespondYN PingResult
01 01 172.XX.XXX.XX Responding 1
01 01 172.XX.XXX.XX Down 0
01 02 172.XX.XXX.XX Responding 1
I've been thinking that I could create the grids by
TestNum showing the results for each building using
either the text in the RespondYN column or my original
thought was to use the value in the PingResult column. At
this point I'm confused and would really appreciate any
ideas or feedback on how to accomplish this task. If more
info is needed I will provide via a reply post. Thank you
very much. And thank you for your patience in reading
this long post.

Dave
 
I am not sure what exactly you mean by a 'grid,' but a couple of ideas
come to mind.

First, create a PivotTable+PivotChart with the data. The TestNum would
be the page field, the BuildingNum the row field, and the (SUM of)
PingResult would be the data field.

Second, sort the data by TestNum. Now, for each group create a chart
with the BuildingNum as the X-data and the PingResult as the Y-data. To
get vertial spikes for responding results create a column chart and set
the Gap Width (double-click the charted series, in the resulting dialog
box select the Options tab). Or, check the Excel/Charts/Step chart page
of my web site.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
 
Hi Tushar,

Thank you for your reply. I guess stating that I need a
table would have been a better description than saying
grid. I will try your ideas and let you know how things
turn out. Thanks again. PS--I like your site, I'm sure
I'll find helpful tips there for future use.
 
Back
Top