Combining Force and Velocity Data in Excel with Irregular Sample Rate

[24forChromium]

Not really a programming question, but if you will:

I have two data series, one is force over time, the other is velocity over time.

The first series has a sample rate of 1 measurement per 0.1 second and is consistent.
The second series has an irregular sample rate (due to the intermittent nature of the photogate) and the time of each measured velocity is precise to the forth decimal, although I can convert them into one decimal place if I have to, the precision there is not terribly important.

I would like to combine the dependent (force and velocity) over just one time variable, how can I do that without mannually dragging the velocity measurements to the matching place for about 2000 times?

 

[berkeman]

Not really a programming question, but if you will:

I have two data series, one is force over time, the other is velocity over time.

The first series has a sample rate of 1 measurement per 0.1 second and is consistent.
The second series has an irregular sample rate (due to the intermittent nature of the photogate) and the time of each measured velocity is precise to the forth decimal, although I can convert them into one decimal place if I have to, the precision there is not terribly important.

I would like to combine the dependent (force and velocity) over just one time variable, how can I do that without mannually dragging the velocity measurements to the matching place for about 2000 times?

I've wondered about how to best do that as well in the past. I used Google to do a search for ways to combine datasets with different time values into one spreadsheet, and this hit seems to have a pretty good answer (last answer in the thread):

http://superuser.com/questions/620810/match-multiple-time-series-columns

You make a single set of time values at your smallest increment, and then use that formula (or similar) to fit each of your two datasets into the overall time line.

The disadvantage of this method is that you will end up with a pretty sparse spreadsheet if you use the 0.1ms time steps that you mentioned. Will your data lose much information if you round up that data to the nearest 10ms, for example?

 

[D H]

Not really a programming question, but if you will:

I have two data series, one is force over time, the other is velocity over time.

The first series has a sample rate of 1 measurement per 0.1 second and is consistent.
The second series has an irregular sample rate (due to the intermittent nature of the photogate) and the time of each measured velocity is precise to the forth decimal, although I can convert them into one decimal place if I have to, the precision there is not terribly important.

I would like to combine the dependent (force and velocity) over just one time variable, how can I do that without mannually dragging the velocity measurements to the matching place for about 2000 times?

An estimation technique such as a Kalman filter would work quite nicely here.

Your force and velocity measurements have errors in them of various sorts. If your measurements were perfect you would only need one velocity measurement. The force (divided by mass) yields acceleration, and this integrates to velocity. Integrating a noisy measurement results in a random walk. A Kalman update with your velocity measurement provide a means to re-anchor that integrated velocity. Your velocity measurements are sporadic. That's not a problem; Kalman filters work quite nicely with sporadic updates.

 

[24forChromium]

An estimation technique such as a Kalman filter would work quite nicely here.

Your force and velocity measurements have errors in them of various sorts. If your measurements were perfect you would only need one velocity measurement. The force (divided by mass) yields acceleration, and this integrates to velocity. Integrating a noisy measurement results in a random walk. A Kalman update with your velocity measurement provide a means to re-anchor that integrated velocity. Your velocity measurements are sporadic. That's not a problem; Kalman filters work quite nicely with sporadic updates.

Unfortunately, the two variables do not relate to each other like that. In fact it is just their relationship that I am trying to find.

 

[BWV]

vlookup is a useful function, you can use the time column in continuous time series to look up matching values in the column with sporadic values. Make sure to set the range_lookup parameter to false

=VLOOKUP([cell in column of continuous data],[cell range for column of sporadic data and column of corresponding value],2 (gets the corresponding value),FALSE)

would need to be combined with the iferror() function if you are calculating something off the data