HOw to plot a line graph to show Speed vs Time of Day

[nsmjc]

Attached is a day of data I Have to identify a speed issue. I need to present this in a chart format indicating the MPH at the different times during the Day. I need to do this for each day during the span of a week the data attached is for one day.

I have tried various way of doing this all and proven to be incorrect.

The time is every 15 minutes
The Speed is across the top in yellow
the numbers in the field represent how many vehicles traveled at that point in time and at the speed.

Any help would be gratefully appreciated.

[MrShorty]

I don't quite understand what you want, and you did not include any of your failed attempts (I sometimes think those failed attempts, especially if you explain why they don't work, can help us see what you are trying to do).

How does "# of vehicles at each speed" figure into the chart that you want to create?
A "contour plot" (number of cars vs time where each speed column is a separate data series) seems like the most obvious choice for a chart based on the way the data table is arranged. Is that what you are looking for?

[Crooza]

There's a lot of data there. Here's how I'd do it.

I've calculated the median speed for each time as well as the 15th percentile and the 85th percentile (the lowest and highest 15%). In Astralia we rely a lot on the 85%percentile speed for deign etc (at least in NSW)

I then charted these three lines.

see attached for better explanation. The only thing you might want to do though is aggregate some of your time slots into 30 minutes so the lines are smoother. .I'm nt convinced you've got enough traffic data to justify 15 minute time slots

[paulketa]

Good day

Please find attached my spreadsheet with a gantt chart. You can refer to workbook "schedule inputs". I want to insert a line showing current date on the gantt schedule.

I have tried to follow quite a number of instructions from google however I keep strange results that are different from what the instructions say e.g.my labels on the vertical axis move down to the horizontal and the current date line appears like a big block which stretches out my dates on the upper labels. I am using Excel 2013

Rgds
Paul

[MrShorty]

@paulketa: this forum can be very strict about not posting your question in another user's thread. Feel free to start your own thread, linking to this thread if you feel that it helps explain your question.

[nsmjc]

Yes that is what we needed I did not think to get the median that makes life much easier.

[Crooza]

great. Glad its what you wanted