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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
byundtMechanical EngineerCommented:
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
byundtMechanical EngineerCommented:
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
byundtMechanical EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
netcmhAuthor Commented:
Excellent! Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.