Synch and scale two scatter plots

SkyeFire

New Member
Joined
Apr 3, 2013
Messages
1
The forum archives have yielded a lot of excellent help for me over the past few years, but this one I'm afraid I need to post a question on.

I'm currently working in a test lab. I have two different instruments recording high speed data from the same event, and I want to import that data into Excel and chart it in such a way that the data are synchronous and on the same time scale.

The issues are:
1: the two instruments sample at different rates -- one every 4ms exactly, the other at roughly 32ms (plus or minus 1-2ms)
2: the two instruments don't start recording at exactly the same time -- one starts automatically, the other has to be started/stopped by hand
3: when I plot both data streams on a scatter plot, one trace always ends up looking "squished" relative to the other along the independent axis
4: even if I could magically sync the start/stop times of both sensors, they still obviously produce very different amounts of data -- the fast sensor can easily have 3-5 times the number of samples than the slow sensor.
5: each sensor time stamp starts from 0 at the beginning of the recording -- I can't synchronize the sensors to an external realtime clock (oh, I wish!)

Importing the data into Excel is not an issue. I have two columns for each sensor: one containing a timestamp for the datum (starting from 0), and the second containing the datum itself. Each data trace can plot on its own scatter plot with no problems, and I can "stack" the plots vertically on my screen and roughly synchronize them by moving the plots and stretching/shrinking them. But that's a rather crude solution. I really want to be able to put these two data streams into the same scatter plot, synchronized and scaled equally along the independent axis.

I can select a particular row to sync the the two streams by hand -- I don't need any sort of automatic event or shape matching (although that would be a neat trick). And if the best way to do this is to write a VBA macro, I've written some modest VBA for data postprocessing before, so I'm not intimidated by that prospect. But before I start chasing down that rabbit hole, I'm hoping that someone here might know of a better, more efficient way to achieve what I'm looking to do.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Norbury

New Member
Joined
Jun 7, 2011
Messages
9
If the data is timestamped then surely you can plot the time along the x-axis and have them line up? There will be the issue of them not having the same origin; that can be addressed by adding a third column to one set of data along the lines of
=A1+0.020
and plotting that as the time. Without an external clock I don't think you could do better than that.

eta: I'm not sure why having different amounts of data should be an issue, but make sure that your x and y ranges are consistent for each line. I mean make sure that it looks like
=SERIES(,Sheet 1!A1:A10,Sheet 1!B1:B10,1)
and not
=SERIES(,Sheet 1!A1:A10,Sheet 1!B1:B20,1)
because I find that can lead to graphs being skewed.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,685
Messages
5,597,531
Members
414,152
Latest member
ReservoirDodds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top