Correcting Speed Graphs on Maps Using Excel

[DaveC426913]

Trying to figure out how to get the curve I want, to plot on a map.
This was my first iteration:

It's wrong.The following sample will illuminate why. For illustration, the sample is much smaller and far more idealized than my real dataset, which is 100+ datapoints.

In my idealized trip, I've driven a course 10km long and taken odometer readings every minute.
(The course is 2 km on a back road, followed by 8km on a highway. The 2km on the back road took me 8 minutes, while the 8km by highway took me only 2 minutes.)

So, I dump all these odometer readings into Excel, and it allows me to produce the Distance / Time graph, but I can also produce the Speed / Time graph.

I took speed the graph itself and superimposed it onto the map, twisting it to match the course.

You can see that is is completely wrong. The leg of the journey from B to C,where I am travellnig at 4km/minute is 4/5ths of the entire course, but the overlay indicates that it is only 1/5th.

I know why it's wrong, what I'm trying figure out is how can I transform the datapoints in Excel so that the speed graph is correct. I'm thinking I have to change the cell formulae to calculate speed per cumulative distance travelled? I'm not sure.

 

[Mentallic]

So you want to plot speed vs. distance.
Your speed can be calculated by

[tex]s_n=\frac{d_n-d_{n-1}}{t_n-t_{n-1}}[/tex]

but since your data is in increments of 1 minute each, your speed (in units km/min) at a particular point is just the cumulative distance at that time minus the cumulative distance traveled at the minute before.

So if you have the time 0-10 in cells A2-A12 and distance markers in cells B2-B12, then just create a speed column in cells C2-C12 where cell C2 is 0 and cell C3 has the value =(B3-B2)/(A3-A2) or simply =B3-B2 because of what I explained earlier, and then just drag down from there to fill all the cells.

The problem I've ran into now is that excel by default skews the x-axis so that only the distance positions you've entered are shown. This means that most of your graph is distance 0-2 while the end jumps to 6 and 10. I don't use excel so I'm honestly not sure how to fix this.

 

[DaveC426913]

So you want to plot speed vs. distance.
Your speed can be calculated by

[tex]s_n=\frac{d_n-d_{n-1}}{t_n-t_{n-1}}[/tex]

but since your data is in increments of 1 minute each, your speed (in units km/min) at a particular point is just the cumulative distance at that time minus the cumulative distance traveled at the minute before.

So if you have the time 0-10 in cells A2-A12 and distance markers in cells B2-B12, then just create a speed column in cells C2-C12 where cell C2 is 0 and cell C3 has the value =(B3-B2)/(A3-A2) or simply =B3-B2 because of what I explained earlier, and then just drag down from there to fill all the cells.

Thank you! This is hugely helpful!

The problem I've ran into now is that excel by default skews the x-axis so that only the distance positions you've entered are shown. This means that most of your graph is distance 0-2 while the end jumps to 6 and 10. I don't use excel so I'm honestly not sure how to fix this.

I'm not sure I follow. What "problem" do you see that needs fixing?

The X-axis is marked in equal increments. How is it skewed?
Most of my graph IS in the 0-2 range because that's where I spent most of my trip.
How would you represent it differently?

 

[Mentallic]

Thank you! This is hugely helpful!

You're welcome :)

I'm not sure I follow. What "problem" do you see that needs fixing?

The X-axis is marked in equal increments. How is it skewed?

Well my x-axis wasn't in equal increments, possibly because of my settings or whatnot. As long as it turned out right for you though.

 

[DaveC426913]

Well my x-axis wasn't in equal increments, possibly because of my settings or whatnot. As long as it turned out right for you though.

Oh, I didn't realize you'd done this yourself.

My sample above was hand-drawn, so that wasn't an issue.

For my real chart, at the top of my OP, I didn't copy anything except copy the polygon itself. You can see there's no X-axis markings at all except those I added manually.

 

[DaveC426913]

Nope. I'm doing it wrong.

$A is odometer reading
$C is $A_n-$A_n-1

$D is $C_n-$A_n-1.

[EDIT] Wait a minute. The X-axis shouldn't be time, it should be distance.

Don't know how to do that in Excel.
[MOAR EDIT] Oh. Scatter plot. I think

 

[Mentallic]

I'm studying at uni at the moment which means I have limited resources. When I get home tonight I'll give it another look.

 

[Mentallic]

So again you want to plot speed on the y-axis versus cumulative distance on the x-axis. The speed is calculated by your C column, so all you have to do is then plot your C column against your A column. But hopefully you can bypass the problem I've had where the x-axis spaces each odometer reading evenly, even though the readings themselves aren't linear, hence skewing the graph.

Also, that D column is meaningless.

 

[DaveC426913]

So again you want to plot speed on the y-axis versus cumulative distance on the x-axis. The speed is calculated by your C column, so all you have to do is then plot your C column against your A column. But hopefully you can bypass the problem I've had where the x-axis spaces each odometer reading evenly, even though the readings themselves aren't linear, hence skewing the graph.

Yes, it's the figuring out how to plot a column of numbers on the X-axis that's tricky. Excel doesn't like to do that.

I think the answer is to abandon line/area graph and go to scatterplot, where you are providing both a Y and an X value for any given coordinate.

Also, that D column is meaningless.

Yes. I was trying to follow your instructions, but clearly failing.

 

[Mentallic]

I think the answer is to abandon line/area graph and go to scatterplot, where you are providing both a Y and an X value for any given coordinate.

Yes that seems to do it!

It's ugly and likely unreasonable, but you could get the area graph to work correctly if you tabulate every odometer increment. So you'd have 0-2 with 0.25 increments with speed 0.25, and then 2-10 with again 0.25 increments with speed 4. It would add a lot of extra data and make things ugly though, so I think the XY scatter plot is the best choice here.

 

[DaveC426913]

but you could get the area graph to work correctly if you tabulate every odometer increment. So you'd have 0-2 with 0.25 increments with speed 0.25, and then 2-10 with again 0.25 increments with speed 4. It would add a lot of extra data and make things ugly though, so I think the XY scatter plot is the best choice here.

Yes, that's the issue, I only have as much data as I have - i.e. one datapoint per minute.

What I have not yet worked out is a way of automatically taking an odometer reading at a given interval. At the moment, I'm doing it manually. i.e. clock ticks over one minute, write odo reading on a piece of paper. This is quite tedious, not to mention dangerous.

Yes, I could automate the entire process all the way to plotting the data on a map, simply by using a GPS system, but that takes all the fun out of it.

 

[Mentallic]

A passenger could help immensely ;)

So does the XY scatterplot do the trick for you? I tried one of the line graphs of that type and it seemed to curve up and around (above speed = 4), which I guess is an attempt to smooth out the edges.

 

[DaveC426913]

A passenger could help immensely ;)

In theory, true. In practice, if I had a passenger, she would almost certainly prefer to chew her arm off rather than spend a two hour trip writing down a number every 60 seconds. :p

So does the XY scatterplot do the trick for you? I tried one of the line graphs of that type and it seemed to curve up and around (above speed = 4), which I guess is an attempt to smooth out the edges.

Turn off the smoothing option.

I'm trying the scatterplot now. At first blush, it does the trick. Now I'm plotting it on the map, and trying to see if it really does line up better than my original attempt.

 

[Mentallic]

In theory, true. In practice, if I had a passenger, she would almost certainly prefer to chew her arm off rather than spend a two hour trip writing down a number every 60 seconds. :p

But Dave, I'm sure you could persuade a few ladies to join you by arguing that they'll be free to do as they wish for the other 59 seconds of each minute :D

Turn off the smoothing option.

I'm trying the scatterplot now. At first blush, it does the trick. Now I'm plotting it on the map, and trying to see if it really does line up better than my original attempt.

Ok and if it doesn't work, you could make good use of the area graph by getting a stopwatch to calculate the time between each distance unit on the odometer (making it as large as you deem reasonable). This can be done quite easily if you use the lap function. This way, you'll have data that will have a linear x-axis because each of your data points will be equal increments in distance.

 

[DaveC426913]

Ok and if it doesn't work, you could make good use of the area graph by getting a stopwatch to calculate the time between each distance unit on the odometer (making it as large as you deem reasonable). This can be done quite easily if you use the lap function. This way, you'll have data that will have a linear x-axis because each of your data points will be equal increments in distance.

Yes. I've considered doing it that way. But the exigencies of driving oblige me to go with the method least taxing on eyes and hands.

 

[DaveC426913]

It definitely lines up nicely.