Pivot tables response time website

Posted on 2011-10-21
Last Modified: 2012-05-12

I urgently need to convert this xls into a graph.
It is the responsetime of a website from one month on a 5 minute basis. The graph should show date, time and response time on that time.
Don't know where I get it wrong but doesn't seem to make it work.

Thanks for your help!
Question by:janhoedt
    LVL 27

    Expert Comment

    by:Glenn Ray
    I suggest inserting a new column and combining the date and time values in that column as your new time (x) axis.

    Additionally, after the chart is created, the x-axis will need to be changed to a text axis.  It will default to a date axis and will only show points on each calendar date, not by the h:mm value.

    Also note that you're plotting more than 6,000 points, this will take time to process each step.  I recommend graphing a smaller set (maybe first thousand points) first, make all the formatting changes to the graph that you want, then extending the data range.

    I've attached an example file with a couple of graphs. responsetimes.xls

    Author Comment

    Thanks! But now I would like to see a line instead of the confusing graph.
    It's a response time of a website, responsetimes are in milliseconds (not seconds as in your Excelsheet) and are taken from different locations. Actually 1 location would be sufficicent + a check every hour would be ok. Do you know how to extract this from the data without manually editing each line?

    Note: do you know also a dummy-guide for pivot-tables?
    LVL 27

    Accepted Solution

    I think the graph is pretty self-evident.  It's very obvious to me that, save for about a half-dozen instances where the response maxes out (30 seconds), the overall response time gradually increased from June to October until abruptly dropping back down to the pre-June levels.  New server maybe?

    Your original data labeled the response time as "seconds", although, I should have surmised that the response time was in milliseconds!  

    With the number of plot points shown, it makes more sense to not show lines connecting the points:  the line implies a trend between points (e.g., a rise in temperature) rather than with discreet measurements, such as shown here.  It also takes much longer to plot.

    If you want to include a trend line, you can do that in the graph simply by right-clicking on the data and choosing "Add trendline..." and choosing an appropriate analysis.

    RE: reducing data set
    Your original data does not show a location for each row, so that would not be possible,  
    Also, your original data does not have a consistent timestamp (ex. every five minutes), but one could add another column that tests for "top of the hour" results (between 0 and 4 minutes after the hour) and plot that instead (I've attached a modified file that does just that).

    RE: Pivot Tables
    I can't recommend any specific introductory guide for using Pivot Tables.  Any good Excel book should do.

    Author Closing Comment

    Great, thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Outlook Free & Paid Tools
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now