How to create a scatter chart in excel?

I have this spreadsheet (attached) and would like to create a scatter chart to show the incidents reported.

I would like the first column (Date) to be on the x-axis and the next two columns (Start and End Time) to be represented on the chart along 24 hours of the day on the y-axis.

I have tried a few variations, but I am unable to get a chart that is helpful.

Please help.

Thank you.
EE.xlsx
LVL 21
netcmhAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
There is no duplication of data.

There is, however, a misleading presentation if the session starts before midnight and ends afterwards such that the midpoint is after midnight. The affected bubbles ought to be moved to the bottom of the chart instead of displayed at the top. Since they are already plotted using the date + time on the X-axis, that part doesn't need to change. I overcame the misleading nature by adding the MOD function to my formula for the midpoint:
=MOD(AVERAGE(B2,IF(C2<B2,1,0)+C2),1)

Open in new window

EE-VPN-Duration-Bubble-chartQ290730.xlsx
1
 
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
The thing with a scatter graph is that all point on the same date will be put at the same X co-ordinate. Perhaps you don't want that? Did you try a chart type that treats the X value as a label rather than a value, such as a line chart?

If that isn't what you are thinking, can you include a drawing of what you expect the chart to look like?
0
 
netcmhAuthor Commented:
I am open to any suggestions that would help show the incidents that occur over the course of a day across the month's data.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
What are you concerned with? The total time spent in a day, # of incidents, or something else?

For example if you want to chart total time, the easiest thing to do is add an additional column that sums up the time for the day and chart that.
0
 
netcmhAuthor Commented:
These are VPN entry logs. I would like to depict the VPN usage across times and dates.
0
 
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
Okay, perhaps something like a line segment between the log on and off time for each entry? Only issue is how to make it visible when you have multiple lines in the same day? I think that can be done with the scatter graph, but I'll have to take some time and figure it out. I'll watch the question to see if any other Experts have ideas.
0
 
netcmhAuthor Commented:
I liked the stock chart with it's open and close prices as the start and stop times, but you're right I don't know how to get it to show productively. I liked the scatter graph idea. The days incrementing along the x axis and the hours incrementing for the day along the y-axis, and the dots to identify each incident. That would give us the trend and help us ascertain maintenance windows with minimal user disturbance.

Would it be easier if we dropped the End time variable column?
0
 
netcmhAuthor Commented:
Any luck?
0
 
byundtCommented:
You might consider a bubble chart with the date on the X-axis, midpoint of session on the Y-axis and duration of the session on the Z-axis (bubble size).
Bubble chartI built a formula for the midpoint in which I added 1 if the ending time was before the starting time.
=AVERAGE(B2,IF(C2<B2,1,0)+C2)

Open in new window

Note that I had to manually specify the increment of Y-axis as 0125 for the major unit (3 hours) and 0416666 as the minor unit (1 hour). I also manually specified the Y-axis span to be 0 to 1.25 (midnight to 3 AM the following day).

When the bubble size looked way too big, I changed its "Scale bubble size" property from 100 to 30.
EE-VPN-Duration-Bubble-chartQ290730.xlsx
1
 
byundtCommented:
Tweak to the preceding post in which I added the date and midpoint time, and used that as the X-axis value. I then changed the X-axis formatting back to the short date number format. Finally, I changed the major unit of the X-axis to 7 so the chart would increment in full weeks.

Appearance is very similar to the previous post, but I believe the results more accurately reflect your data.
EE-VPN-Duration-Bubble-chartQ290730.xlsx
1
 
netcmhAuthor Commented:
Brilliant! Is there any way to consolidate the midnight to 3am duplication?
0
 
netcmhAuthor Commented:
Excellent! Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.