Scatter Chart Formatting issue

[nawGo]

Hi,

I have attached a sample data and chart which resemble my real data a lot (Thanks Andy!)

I am having some formatting issues for points that are colliding ( 2 : 05 am on Monday and Wednesday) and data that is near to each other eg. ( Monday 1: 05 AM)

How do I make the data shapes visible? Or how do I make the graph easier to understand? your feedback is appreciated.

Thanks

1027856.xlsx

[MrShorty]

Observation 1: Not a big deal, but to be exact, your example is a line chart, not a scatter chart. This might be worth reading to try to understand the difference: http://office.microsoft.com/en-us/ex...478.aspx?CTT=1

Observation 2: Wednesday data point #4 is a text string that looks like a time rather than a real number. Excel is going to treat this as "0" rather than a time just before midnight at the end of the day.

Finally: how to make the graph easier to understand? The first part of answering that question is a clear explanation (at least in your own mind) of what you are trying to get the graph to communicate. As one who has no idea what you are trying to communicate, all I see is "day of the week" across the x axis and "time of day" on the y axis. Most of the data points are very early morning, with a couple of late evening data points. Are you trying to show how the data are clustered around certain times of the day? Or how the time stamp changes from day to day?

[nawGo]

Hi,

Great observations. This is a sample draft, I can't use the real data as it's confidential.
However, I will try my best to explain what I am trying to achieve.

Basically a company has multiple sites around the world and the head quarter is in Canada.
The site in Canada receives test scripts from these worldly sites at different Canadiandays/times. In some cases the test scripts are received at the same Canadian time/day causing collision.

e.g. (Data label 3 is Site in India and we receive test script 1 at 2.05 am weekly on Monday, we also receive test script 2 from the Singaporean site at 2.05 am).

I want the graph to clearly show the site names and Canadian time we receive the test scripts at?
I hope I was able to make things clear, thanks for asking..Feel free to ask more!

[MrShorty]

If I understand, you are trying to analyze and describe what is going on with some of these clusters.

I might suggest a panel chart like this described by Jon Peltier: http://peltiertech.com/Excel/ChartsH...venScales.html Using a format like this, you can have one panel to show the entire 24 hour period where you can see what time periods have "clusters" of data and which ones are well separated from others. Then you can have additional panels for each "cluster" period (maybe 1:50 to 2:20 AM for the sample data points) that will show in more detail just how tightly clustered this particular subset is and which ones are in danger of "colliding". If you think a panel chart seems appropriate, go through Jon's tutorial carefully to understand how it works. If there are details you have trouble understanding, let us know and we'll help as best we can.

[nawGo]

Hi, thanks for your suggestion and website
I was thinking more along the lines of making the data jitter https://www.youtube.com/watch?v=frMEQcMB77E is there a way to do this using time? kindly let me know

[MrShorty]

"Jittering" as the video describes is not difficult -- it is simply adding a small random number to each value so that they are no longer all exactly the same. Since Excel sees time as a number (formatted to display time), once you understand how Excel stores date/time values as numbers, it should be easy to do a similar thing to dates/times: http://www.cpearson.com/excel/datetime.htm

My main objection to "jittering" is that the plotted values on the chart are no longer the same values as the original data. This has the potential to create confusion among readers who are expecting the tabulated and the plotted values to match up. If the ability to correctly identify "collisions" is an important part of this chart, I would be concerned that "jittering", because it alters the plotted data, might interfere with the ability to identify "collisions". Only you and your readers can judge whether this is the case, so try it and see what you get and whether you like it or not.