Problem with Excel

  • #1

Main Question or Discussion Point

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?
 

Answers and Replies

  • #2
berkeman
Mentor
57,716
7,750
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?
 
  • #3
D H
Staff Emeritus
Science Advisor
Insights Author
15,393
683
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.
 
  • Like
Likes berkeman
  • #4
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.
 
  • #5
BWV
624
541
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
 

Related Threads on Problem with Excel

  • Last Post
Replies
10
Views
11K
  • Last Post
Replies
10
Views
32K
  • Last Post
Replies
4
Views
2K
  • Last Post
Replies
3
Views
2K
Replies
1
Views
637
  • Last Post
Replies
2
Views
3K
  • Last Post
Replies
1
Views
531
  • Last Post
Replies
18
Views
4K
  • Last Post
Replies
2
Views
2K
Top