2 sets data, different time sampling rates, want to compare from common time.

Hi guys, first post here but I`m hoping someone can point me in the right direction. I seem to have had a brain freeze on this one and can`t work out what to do. I`m sure its blindingly obvious, but I`m missing it.

I have 2 sets of data (one from a GPS logger and one from an ECU datalogger)

The GPS data is sampled every 0.1 seconds. The data I want is in the following format

Please Login or Register  to view this content.

The ECU data is in this format

Please Login or Register  to view this content.

The GPS sample rate is 0.1 seconds between samples (using sample rate = A* - (a*-1)

The ECU sample rate varies, which is causing me problems. Using the same formula to subtract the lower time value from the one after, I get a time difference between cells of between 0.061 and 0.072.

What I want to do is create a new sheet, with the the time in column 1, GPS data column 2 and a new column with the interpolated RPM in the next column.



There can be a LOT of data, an hours worth of GPS data is 36,000 lines of information.


I`m certain there is a blindingly obvious formula to calculate a rolling interpolated value or a simple macro, but , I`m stuck. I can`t get out of my mind the ideas I have and I`m going in circles.

Can anyone help please ?


The reason I want to do this is the next step is to look at the RPM and speed and complete a formula to calculate the gear I`m in, so I can overlay this data on the incar video. Calculating the gear from speed / rpm is pretty easy, the speed to rpm ratio per gear is pretty static (only changes with wheelspin or skids), so I can sort that fairly easily, I think.

120+ views and nobody has even a suggestion of how I can do this ?

Last post. Anyone with a suggestion of a website / forum where I may get some advice please ?

1st thing I notice -- the two sets of time stamps are different. The first step I see for this problem is some way to correlate to two time stamps so we can identify which interval on the GPS log corresponds to which interval on the ECU log. Perhaps an elapsed time column (ET=TS(i)-TS(0)) assuming the first entry in each data set is time 0 or whatever seems appropriate.

Excel doesn't have a built in linear interpolation function (I know of at least one other spreadsheet program that does if you are not married to Excel), so you have to do your own linear interpolation. User shg has posted a UDF that will perform linear interpolations, you might put that into the site's search engine if you want a copy of his solution. Building your own solution isn't difficult, but it does require several steps:
1) Lookup the interval containing the desired time/time stamp. =MATCH() works well for this.
2) return the four values (2 time values and 2 rpm values) that bracket the interval located in step 1. I usually use the =INDEX() function for this.
3) Use a function for a straight line (=TREND() is one possibility) using the four values returned in step 2 to find rpm at the desired time/time stamp value.

That's how I would approach the problem.