Gap against Speed

[Probability]

So its not a college course but what I refer to as CPD training. I'm working my way through some maths training books at this time and just starting to look at graphs. This particular graph I have plotted using Excel, now I'm no expert with Excel either so my reason for posting this is to try and get some feedback on what I can actually do using Excel.

In the graph attached I have plotted distance in metres against speed in m/s. This is the blue line with plots on it. I was then asked to plot a time line which is shown in red. A question was then asked to find the distance in metres for a vehicle traveling at 25 m/s. This is easy but I wanted to use Excel software to plot it but I don't think Excel is able to plot vertical or horizontal lines?

Would anyone have any experience using Excel and advise me if this is possible please?

Now another question after trying to upload the graph, the maths forum software does not allow the Excel extension to be uploaded, anything I can do?



I've done a copy and paste.

 

[lev888]

So its not a college course but what I refer to as CPD training. I'm working my way through some maths training books at this time and just starting to look at graphs. This particular graph I have plotted using Excel, now I'm no expert with Excel either so my reason for posting this is to try and get some feedback on what I can actually do using Excel.

In the graph attached I have plotted distance in metres against speed in m/s. This is the blue line with plots on it. I was then asked to plot a time line which is shown in red. A question was then asked to find the distance in metres for a vehicle traveling at 25 m/s. This is easy but I wanted to use Excel software to plot it but I don't think Excel is able to plot vertical or horizontal lines?

Would anyone have any experience using Excel and advise me if this is possible please?

Now another question after trying to upload the graph, the maths forum software does not allow the Excel extension to be uploaded, anything I can do?

View attachment 18245

I've done a copy and paste.

Could you explain the 2 graphs? I think I understand the red one - it shows the stopping distance (vertical axis) for the given speed (horizontal axis). What about the blue one?

 

[Dr.Peterson]

In the graph attached I have plotted distance in metres against speed in m/s. This is the blue line with plots on it. I was then asked to plot a time line which is shown in red. A question was then asked to find the distance in metres for a vehicle traveling at 25 m/s. This is easy but I wanted to use Excel software to plot it but I don't think Excel is able to plot vertical or horizontal lines?

Would anyone have any experience using Excel and advise me if this is possible please?

View attachment 18245

You say the blue line is (stopping?) distance, but it's labeled as speed. Did you label the lines wrong? But if the red line is really speed, converted from m/s to km/h, it isn't correct. I'm not sure what you mean by "time line"; the red appears to be just twice the speed, in m/s. Perhaps you can show us the formulas you used for the data.

As for your actual question, the way to have Excel actually calculate the stopping distance at an intermediate speed would be to create a trendline, display its equation, and use that equation to calculate an actual value. This assumes that some form of trendline would be reasonably accurate (perhaps a power function or a polynomial).

I'm not sure about vertical lines in general (apart from using the drawing tool, "shapes"), but if you turn on grid lines in the graph, that would help a lot.

 

[Probability]

Could you explain the 2 graphs? I think I understand the red one - it shows the stopping distance (vertical axis) for the given speed (horizontal axis). What about the blue one?

Thank you for your reply. We must remember this is a learning curve for me so all misunderstandings are mine alone. The blue line would probably have been better worded as "Stopping distance" in metres. See Dr Peterson's post and my reply to it for further clarification.

 

[Probability]

You say the blue line is (stopping?) distance, but it's labeled as speed. Did you label the lines wrong? But if the red line is really speed, converted from m/s to km/h, it isn't correct. I'm not sure what you mean by "time line"; the red appears to be just twice the speed, in m/s. Perhaps you can show us the formulas you used for the data.

As for your actual question, the way to have Excel actually calculate the stopping distance at an intermediate speed would be to create a trendline, display its equation, and use that equation to calculate an actual value. This assumes that some form of trendline would be reasonably accurate (perhaps a power function or a polynomial).

I'm not sure about vertical lines in general (apart from using the drawing tool, "shapes"), but if you turn on grid lines in the graph, that would help a lot.

Yes the blue line is stopping distance, but I think the confusion is that I'm not talking about a vehicle stopping from say 60 MPH to 0 MPH in a given time, what I'm trying to say is and show is that at any given speed m/s let's say 31 m/s then when the vehicle brakes to "slow down" but not stop, then the distance the vehicle would need to fully stop would be 96 metres, but we are only talking about a vehicle decelerating from 31 m/s for 2 seconds and hence the "time gap" in metres between the vehicle doing the braking and the vehicle in front will be 62 metres.

So a distance needs to be calculated, hence using a formula such as; d = s x t for simplicity.

d = distance,
s = speed,
t = time.

So in my example above, 31 m/s x 2 = 62 m.

Now if we refer to the graph as visual data of the above, then choosing a speed in m/s, say 25 m/s, without needing to remember or find the formula, the graph shows 25 m/s along the X axis and by drawing a vertical line to "Time Model" which is the red line, we can read off the graph without any maths and know that the distance in metres to slow down in 2 seconds is 50 metres.

What the graph shows is that it is not very reliable. The first visual understanding is the "blue line". The reader should be able to see that this line is not straight but is curved. The distance model shows a smaller gap up to a speed of about 18 m/s. Now when speeds increase over and above 18 m/s, the time model then shows a smaller gap, and this then shows a discrepancy between this model and the distance model increasing quite rapidly as the speed increases, hence the distance model graph as said previously is not a straight line.

If the reader mentally draws the vertical line from 25 m/s to the red line and then draws the horizontal line to the vertical axis, then the gap distance will read as said 50 m, but if the reader then continues to draw the vertical line from the red line up to the blue line, then the reader drawing another horizontal line will see a new gap distance on the vertical axis for the same 25 m/s speed, hence approx 62 - 65 metres.

I hope I've explained the above reasonably well, but my primary interest is in using the excel software to produce better more detailed graphs.

 

[Dr.Peterson]

Yes the blue line is stopping distance, but I think the confusion is that I'm not talking about a vehicle stopping from say 60 MPH to 0 MPH in a given time, what I'm trying to say is and show is that at any given speed m/s let's say 31 m/s then when the vehicle brakes to "slow down" but not stop, then the distance the vehicle would need to fully stop would be 96 metres, but we are only talking about a vehicle decelerating from 31 m/s for 2 seconds and hence the "time gap" in metres between the vehicle doing the braking and the vehicle in front will be 62 metres.

I hope you recognize that my comment was about the key on the graph, which labels the blue line as "speed km/h", and the red line as stopping distance. You're confirming that the graph is somehow labeled wrong, correct? I was paying no attention to whether the stopping distances are what I expect. Now, if anything, I'm more confused. A "stopping distance" that isn't a stopping distance is hard to follow.

Now if we refer to the graph as visual data of the above, then choosing a speed in m/s, say 25 m/s, without needing to remember or find the formula, the graph shows 25 m/s along the X axis and by drawing a vertical line to "Time Model" which is the red line, we can read off the graph without any maths and know that the distance in metres to slow down in 2 seconds is 50 metres.

But the red line is labeled "Stopping distance in metres", not "Time Model", though what you describe it as meaning is what it shows, 2 times the speed. I'm getting dizzy.

What the graph shows is that it is not very reliable. The first visual understanding is the "blue line". The reader should be able to see that this line is not straight but is curved. The distance model shows a smaller gap up to a speed of about 18 m/s. Now when speeds increase over and above 18 m/s, the time model then shows a smaller gap, and this then shows a discrepancy between this model and the distance model increasing quite rapidly as the speed increases, hence the distance model graph as said previously is not a straight line.

If the reader mentally draws the vertical line from 25 m/s to the red line and then draws the horizontal line to the vertical axis, then the gap distance will read as said 50 m, but if the reader then continues to draw the vertical line from the red line up to the blue line, then the reader drawing another horizontal line will see a new gap distance on the vertical axis for the same 25 m/s speed, hence approx 62 - 65 metres.

I hope I've explained the above reasonably well, but my primary interest is in using the excel software to produce better more detailed graphs.

And my primary concern is that nothing seems to be labeled right. Maybe it's just that your terminology is foreign to me.

Back to your interest:

Did you try turning on the grid lines? How about changing the spacing of tick marks on the axis to give finer resolution when you read speeds?

 

[Probability]

Sorry for the confusion. I have four columns of data and I seem to have confused the labels. What I've done is to write the wrong label down as the heading for the vertical column of data. I just need some more practice to get it correct.

I've now added the grids and looking at the graph it is now possible to look for comparisons between the Y axis and the X axis data.

I'll do a few more graphs to help my understanding and build confidence. Thank you for your help.

 

[Probability]

Here is an updated graph, hopefully this time I've got it correct!

 

[Dr.Peterson]

The grid definitely helps; and the fact that you have no legend means I can't complain about the labeling ...

But the linear trendline for the blue curve is not meaningful. I'm not sure you ever said how you calculated those numbers (and I haven't been paying any attention to whether any of your actual numbers are valid!), but did you try other kinds of trend, such as power or polynomial? They're definitely worth learning about.

 

[Probability]

Thank you Dr Peterson, Excel is also a new learning curve for me and the trend line you refer to I did not put that in, the software itself did that. It seems at the outset that Excel has a few samples of graphs that can be selected. Looking at my example I had to decide which type of graph format would produce the type of graph I was drawing, if that makes sense. The numbers you refer to (I think) along the graphs plotted points can be manual put in using the excel software, as I did. Just to clarify the plotted points numbers along the lines, they are from the table of data, i.e. the distance gap in metres and the time model also in metres, and again the positioning and spacing of this data is also controlled by the software, which does not make it easy to read it and decide which line it refers to when the data is so closely positioned together and read from the graph.

 

[Dr.Peterson]

Thank you Dr Peterson, Excel is also a new learning curve for me and the trend line you refer to I did not put that in, the software itself did that. It seems at the outset that Excel has a few samples of graphs that can be selected. Looking at my example I had to decide which type of graph format would produce the type of graph I was drawing, if that makes sense. The numbers you refer to (I think) along the graphs plotted points can be manual put in using the excel software, as I did. Just to clarify the plotted points numbers along the lines, they are from the table of data, i.e. the distance gap in metres and the time model also in metres, and again the positioning and spacing of this data is also controlled by the software, which does not make it easy to read it and decide which line it refers to when the data is so closely positioned together and read from the graph.

I didn't even know that Excel had an option that automatically put in a trendline. (I don't have the latest version.) To change it, right-click on that line and you should see a list of options, including exponential, linear, and so on. Experiment. Or you can just turn it off.

I didn't refer to the numbers printed on the graph (which is an option I may never have used). To change or remove them, right-click on a "marker" (dot) and select "Format data labels". When I said I don't know whether your numbers are valid, I meant the values or formulas you entered in cells, not the fact that they are displayed.

There is a lot to learn! Feel free to keep playing with options, and ask more questions (though there are other forums devoted to Excel, that might be more appropriate in some ways).