Cross Correlation analysis with two series of wavelets

MrSak87

New Member
Joined
Jan 8, 2015
Messages
44
Hey all,

This one will be a tough one to explain...

I essentially have 3 columns of data and 40000+ rows of it. Column A measures seconds from time 0 to say 10. Column B is a series of values creating on a graph wavelets from a source detected by receiver one and Column C is the same series of wavelets detected by receiver two. The two receivers are relatively closely spaced apart and therefore the profiles of these waves will be very similar although NOT identical. The spacing between the wavelets also changes due to the density of the soil/rock they travel throughThe aim is for excel to detect the peaks of each correlating wave and detect the lag time between the two series at each source. An example of a lag between two wavelets is shown

https://www.google.co.uk/search?q=w...o.com%2Fcalculator-timedelayphase.htm;365;152

This lag time calculation (column 4!?) allows me to do all the statistical analysis for my job. The traditional way is to manually pick the peaks using ancient software but this simply isn't feasible with the amount of data I have.

My VB ability is marginally better than just recording but only slightly.

Please help!
Sean
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,997
show us the raw data that produces a wave - my first thought, if there is enough data, is to detect the max value and take the average of it and say 10 data values either side
 

MrSak87

New Member
Joined
Jan 8, 2015
Messages
44
Seconds x1 x2
0-5.87E-05-1.00E-04
0.000417-1.05E-05-2.16E-05
0.0008334.64E-07-1.61E-05
0.00125-5.53E-06-2.32E-05
0.001667-4.74E-06-2.16E-05
0.002083-6.32E-06-2.21E-05
0.00252.38E-06-9.34E-06
0.0029172.43E-052.88E-05
0.0033332.85E-054.73E-05
0.003752.64E-054.24E-05
0.0041673.66E-055.96E-05
0.0045833.98E-057.17E-05
0.0054.37E-057.63E-05
0.0054175.28E-058.52E-05
0.0058335.39E-058.75E-05
0.006255.18E-058.56E-05
0.0066675.28E-058.54E-05
0.0070835.40E-059.17E-05
0.00755.90E-059.97E-05
0.0079171.08E-041.60E-04
0.0083331.45E-041.99E-04
0.008751.39E-041.99E-04
0.0091671.36E-041.80E-04
0.0095831.30E-041.80E-04
0.011.16E-041.54E-04
0.0104176.84E-057.92E-05
0.0108336.07E-056.89E-05
0.011256.62E-058.04E-05
0.0116676.45E-057.60E-05
0.0120836.16E-057.48E-05
0.01255.05E-055.67E-05
0.0129172.93E-052.13E-05
0.0133331.75E-05-2.09E-06
0.013752.20E-052.43E-06
0.0141678.96E-06-1.58E-05
0.0145831.10E-06-3.22E-05
0.015-1.89E-06-4.00E-05
0.015417-1.07E-05-4.75E-05
0.015833-1.69E-05-4.91E-05
0.01625-1.32E-05-4.71E-05
0.016667-1.58E-05-4.91E-05
0.017083-2.13E-05-5.86E-05
0.0175-2.92E-05-6.76E-05
0.017917-8.34E-05-1.38E-04
0.018333-1.14E-04-1.75E-04
0.01875-1.12E-04-1.26E-04
0.019167-6.75E-05-1.33E-04
0.019583-9.43E-05-1.21E-04
0.02-7.51E-05-1.01E-04
0.020417-4.83E-05-4.70E-05
0.020833-3.14E-05-2.24E-05
0.02125-4.12E-05-3.68E-05
0.021667-3.43E-05-3.13E-05
0.022083-3.65E-05-3.23E-05
0.0225-3.42E-05-2.28E-05
0.022917-1.72E-051.71E-06
0.023333-1.95E-063.21E-05
0.02375-5.21E-062.40E-05
0.0241677.12E-063.62E-05
0.0245831.91E-055.31E-05
0.0252.52E-055.70E-05
0.0254173.56E-056.15E-05
0.0258333.97E-055.74E-05
0.026253.76E-055.69E-05
0.0266674.27E-055.82E-05
0.0270835.11E-056.80E-05
0.02755.87E-057.57E-05
0.0279171.05E-041.29E-04
0.0283331.49E-041.81E-04
0.028751.41E-041.70E-04
0.0291671.40E-041.70E-04
0.0295831.41E-041.66E-04
0.031.27E-041.43E-04
0.0304179.39E-058.50E-05
0.0308338.17E-057.44E-05
0.031258.67E-058.38E-05
0.0316678.48E-058.61E-05
0.0320838.74E-058.60E-05
0.03258.33E-057.90E-05
0.0329177.50E-055.40E-05
0.0333336.36E-052.61E-05
0.033756.23E-053.00E-05
0.0341675.24E-051.50E-05
0.0345834.81E-052.19E-06
0.0353.70E-05-2.96E-06
0.0354173.02E-05-5.15E-06
0.0358332.32E-05-3.19E-06
0.036252.56E-053.55E-07
0.0366672.18E-051.55E-06
0.0370831.80E-05-2.66E-06
0.03751.01E-05-1.41E-05
0.037917-5.43E-05-9.54E-05
0.038333-6.99E-05-1.13E-04
0.03875-6.85E-05-1.07E-04
0.039167-6.75E-05-1.03E-04
0.039583-6.48E-05-9.57E-05
0.04-5.21E-05-6.97E-05
0.040417-1.66E-05-8.30E-06
0.040833-7.11E-061.10E-06
0.04125-9.89E-06-5.37E-06
0.041667-1.06E-05-7.34E-06
0.042083-1.41E-05-1.01E-05
0.0425-6.71E-064.14E-06
0.0429176.25E-082.92E-05
0.0433337.30E-065.10E-05
0.043758.75E-064.48E-05
0.0441671.70E-055.52E-05
0.0445831.98E-056.75E-05
0.0452.63E-057.14E-05
0.0454173.58E-057.56E-05
0.0458333.74E-056.91E-05
0.046253.74E-056.47E-05
0.0466674.09E-056.02E-05
0.0470835.32E-056.52E-05
0.04756.87E-057.24E-05
0.0479171.31E-041.35E-04
0.0483331.76E-041.76E-04
0.048751.72E-041.63E-04
0.0491671.68E-041.61E-04
0.0495831.57E-041.52E-04
0.051.37E-041.33E-04
0.0504179.06E-057.19E-05
0.0508336.81E-056.91E-05
0.051256.81E-059.16E-05
0.0516676.27E-051.04E-04
0.0520835.57E-051.10E-04
0.05253.63E-058.89E-05
0.0529171.58E-054.87E-05
0.0533338.46E-061.64E-05
0.053751.13E-051.98E-05
0.0541671.13E-051.71E-06
0.0545832.28E-05-1.23E-05
0.0552.99E-05-1.81E-05
0.0554173.27E-05-2.28E-05
0.0558333.74E-05-2.92E-05
0.056254.59E-05-3.58E-05
0.0566675.21E-05-3.75E-05
0.0570835.88E-05-4.37E-05
0.05756.03E-05-4.56E-05
0.0579171.30E-05-9.69E-05
0.058333-3.01E-05-1.25E-04
0.05875-3.79E-05-1.02E-04
0.059167-6.36E-05-9.18E-05
0.059583-8.66E-05-7.56E-05
0.06-1.01E-04-4.96E-05
0.060417-8.71E-052.29E-05
0.060833-9.01E-054.24E-05
0.06125-1.03E-043.17E-05
0.061667-1.12E-042.52E-05
0.062083-1.21E-048.89E-06
0.0625-1.24E-04-7.00E-06
0.062917-1.11E-043.93E-06
0.063333-1.07E-04-4.31E-06
0.06375-9.72E-05-2.73E-05
0.064167-7.35E-05-2.44E-05
0.064583-5.09E-05-1.46E-05
0.065-2.99E-05-1.21E-05
0.065417-1.64E-05-1.50E-05
0.065833-1.42E-05-2.74E-05
0.06625-1.62E-05-3.41E-05
0.066667-1.78E-05-3.41E-05
0.067083-9.77E-06-1.65E-05
0.06752.99E-066.24E-06
0.0679175.31E-058.11E-05
0.0683331.01E-041.45E-04
0.068759.30E-051.41E-04
0.0691679.13E-051.42E-04
0.0695838.50E-051.26E-04
0.077.06E-051.03E-04
0.0704173.97E-053.95E-05
0.0708333.97E-053.43E-05
0.071255.80E-055.52E-05
0.0716677.04E-055.92E-05
0.0720837.43E-055.59E-05
0.07257.38E-053.49E-05
0.0729175.84E-05-8.99E-06
0.0733336.91E-05-2.55E-05
0.073758.75E-05-1.27E-05
0.0741679.86E-05-1.90E-05
0.0745831.09E-04-2.13E-05
0.0751.20E-04-1.69E-05
0.0754171.20E-04-1.35E-05
0.0758331.20E-04-5.45E-06
0.076251.27E-043.02E-06
0.0766671.30E-049.92E-06
0.0770831.30E-041.34E-05
0.07751.27E-042.26E-05
0.0779177.65E-05-2.46E-05
0.0783333.20E-05-5.72E-05
0.078752.71E-05-3.99E-05
0.0791678.17E-06-3.56E-05
0.079583-1.37E-07-2.47E-05
0.085.73E-064.25E-06
0.0804174.17E-057.24E-05
0.0808335.02E-058.53E-05
0.081253.78E-057.21E-05
0.0816672.81E-056.80E-05
0.0820831.36E-056.02E-05
0.08255.94E-066.35E-05
0.0829179.02E-069.83E-05
0.0833332.33E-061.13E-04
0.08375-2.20E-069.83E-05
0.0841673.62E-061.08E-04
0.0845833.91E-061.11E-04
0.0853.76E-061.08E-04
0.0854171.52E-069.89E-05
0.085833-7.79E-068.30E-05
0.08625-2.08E-056.92E-05
0.086667-2.47E-055.99E-05
0.087083-3.02E-056.28E-05
0.0875-2.91E-056.58E-05
0.0879171.53E-051.19E-04
0.0883335.31E-051.60E-04
0.088753.94E-051.31E-04
0.0891673.74E-051.21E-04
0.0895833.74E-059.89E-05
0.093.10E-056.90E-05
0.0904171.12E-06-6.51E-06
0.0908337.02E-06-1.85E-05
0.091252.86E-05-3.42E-06
0.0916674.07E-053.16E-07
0.0920835.32E-057.17E-08
0.09255.77E-05-1.32E-05
0.0929174.67E-05-4.77E-05
0.0933335.19E-05-5.85E-05
0.093756.39E-05-4.37E-05
0.0941676.50E-05-4.44E-05
0.0945836.47E-05-4.77E-05
0.0955.75E-05-3.81E-05
0.0954174.84E-05-3.27E-05
0.0958334.23E-05-1.81E-05
0.096254.97E-05-3.87E-06
0.0966675.77E-051.04E-05
0.0970836.76E-051.27E-05
0.09757.43E-051.14E-05
0.0979173.52E-05-4.99E-05
0.0983331.42E-05-9.43E-05
0.098753.72E-05-8.13E-05
0.0991675.16E-05-8.16E-05
0.0995836.85E-05-6.28E-05
0.18.66E-05-3.01E-05

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,997
I plotted the data and the peak from the last column is higher, but both peak widths are the same. What info would you like. I do not think it is possible to detect any lag between the peaks hitting a maximum because there are very few data points maybe 5 that define the peak
 

MrSak87

New Member
Joined
Jan 8, 2015
Messages
44
Sorry my apologies, I pasted the top of the data then went home after work. The top of the data doesn't show anything really. This shows a bit more. Peak or trough is required. So in this data set the troughs would be appropriate

3.4950.0031150.001985
3.4954170.0032940.001946
3.4958330.0034290.001883
3.496250.0035480.001806
3.4966670.0036390.00173
3.4970830.0037090.001679
3.49750.003740.001686
3.4979170.0037030.001711
3.4983330.0035560.001727
3.498750.0034120.00184
3.4991670.0032090.00191
3.4995830.0029880.00197
3.50.0027360.002007
3.5004170.0025390.002056
3.5008330.002380.002056
3.501250.0022440.001977
3.5016670.0021320.001892
3.5020830.0020120.001765
3.50250.0018650.001573
3.5029170.0016940.001352
3.5033330.0014660.00108
3.503750.0012040.000792
3.5041670.0009260.000546
3.5045830.0006110.000363
3.5050.0002160.000193
3.505417-0.000271.13E-05
3.505833-0.00083-0.0002
3.50625-0.00146-0.00044
3.506667-0.0021-0.0007
3.507083-0.00272-0.00093
3.5075-0.0033-0.00116
3.507917-0.00379-0.00136
3.508333-0.00414-0.00153
3.50875-0.00447-0.00182
3.509167-0.0047-0.00212
3.509584-0.00487-0.00245
3.51-0.005-0.00281
3.510417-0.00507-0.00321
3.510834-0.00503-0.00353
3.51125-0.0049-0.00374
3.511667-0.00471-0.00392
3.512084-0.00446-0.00408
3.5125-0.00413-0.00423
3.512917-0.00377-0.00436
3.513334-0.00335-0.00441
3.51375-0.00291-0.00437
3.514167-0.00247-0.00429
3.514584-0.00203-0.00419
3.515-0.00159-0.00403
3.515417-0.00119-0.00382
3.515834-0.00081-0.00354
3.51625-0.00046-0.00321
3.516667-0.00015-0.00284
3.5170840.000113-0.00245
3.51750.000305-0.00205
3.5179170.000438-0.00168
3.5183340.000519-0.00134
3.518750.000652-0.00089
3.5191670.000708-0.00047
3.5195840.000729-3.4E-05
3.520.0006870.000389
3.5204170.0006080.000794
3.5208340.0004950.001104
3.521250.0003240.001285
3.5216679.63E-050.001408
3.522084-0.000160.001494
3.5225-0.000410.00155
3.522917-0.000590.001613
3.523334-0.000740.001605
3.52375-0.00090.001497
3.524167-0.001040.001325
3.524584-0.001120.001144
3.525-0.001170.000966
3.525417-0.001170.000823
3.525834-0.001080.000694
3.52625-0.000910.000594
3.526667-0.00070.000532
3.527084-0.000480.000521
3.5275-0.000250.000531
3.5279177.3E-060.000578
3.5283340.0003490.000716
3.528750.000630.000816
3.5291670.0009280.001012
3.5295840.00120.001246
3.530.0014660.001472
3.5304170.001670.001611
3.5308340.0018490.001731
3.531250.0020030.001878
3.5316670.0020960.001979
3.5320840.0021820.002091
3.53250.0022850.002206
3.5329170.0024060.002279
3.5333340.0025510.002348
3.533750.0027060.002393
3.5341670.0028520.002379
3.5345840.0029820.002315
3.5350.0031030.002254
3.5354170.0032050.002189
3.5358340.0032910.002122
3.536250.0033710.002048
3.5366670.0034420.00195
3.5370840.0034790.001838
3.53750.0034890.001761
3.5379170.0034590.001702
3.5383340.0033740.001643
3.538750.0033420.00171
3.5391670.0032630.001756
3.5395840.0031650.001798
3.540.0030170.001825
3.5404170.0028740.001881
3.5408340.0026820.001888
3.541250.0024490.001827
3.5416670.002220.00178
3.5420840.0019990.001719
3.54250.0017650.001628
3.5429170.0015660.001529
3.5433340.001350.001367
3.543750.0011210.001136
3.5441670.000860.000885
3.5445840.0005360.000623
3.5450.0001380.000342
3.545417-0.000296.72E-05
3.545834-0.00077-0.00019
3.54625-0.00132-0.00044
3.546667-0.00191-0.00068
3.547084-0.00251-0.00091
3.5475-0.00311-0.00115
3.547917-0.00366-0.00138
3.548334-0.00412-0.00158
3.54875-0.00458-0.00188
3.549167-0.00492-0.00215
3.549584-0.00515-0.00243
3.55-0.00526-0.00272
3.550417-0.00531-0.00307
3.550834-0.00526-0.00339
3.55125-0.00508-0.00366
3.551667-0.00483-0.00394
3.552084-0.00451-0.00418
3.5525-0.00415-0.00438
3.552917-0.00378-0.00454
3.553334-0.00335-0.00461
3.55375-0.0029-0.00459
3.554167-0.00247-0.00451
3.554584-0.00208-0.0044
3.555-0.00173-0.00422
3.555417-0.00141-0.004
3.555834-0.00108-0.00372
3.55625-0.00077-0.00338
3.556667-0.00047-0.00298
3.557084-0.00019-0.00257
3.55758.75E-05-0.00215
3.5579170.000319-0.00176
3.5583340.000493-0.00143
3.558750.000688-0.00102
3.5591670.000791-0.00064
3.5595840.000846-0.00024
3.560.0008620.000145
3.5604170.0008330.000543
3.5608340.0007380.000879
3.561250.000560.001115
3.5616670.0003550.001324
3.5620840.0001470.001476
3.5625-7.3E-050.001586
3.562917-0.000270.001671
3.563334-0.000460.001679
3.56375-0.000650.0016
3.564167-0.000790.001485
3.564584-0.000870.00136
3.565-0.000920.001206
3.565417-0.000920.001057
3.565834-0.000890.000903
3.56625-0.000850.000769
3.566667-0.000790.000657
3.567084-0.000720.000595
3.5675-0.000640.000576
3.567917-0.000490.000638
3.568334-0.000210.000808
3.568754.79E-050.000932
3.5691670.0003850.001122
3.5695840.000770.001288
3.570.0011970.001422
3.5704170.0015650.001463
3.5708340.0018840.001503
3.571250.0021410.001604
3.5716670.0023220.001716
3.5720840.0024670.001869
3.57250.0025830.002053
3.5729170.002650.002205
3.5733340.0027260.002355
3.573750.0028150.002467

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>


Is the kind of thing I'm looking for possible? Cheers for your reply
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,997
I used conditional formatting to find all the minima in cols B and C using =and(b2<b1,b2<b3)

are you interested in the time difference between each pair of minima or the minimum value itself

PM me if you want to continue off line as I think there is nothing fundamental of interest to the general forum readers
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,752
Office Version
2010
Platform
Windows
Uisng formulas, you can see that the correlation of the first 150 samples of W1 peaks with a 5-sample delay to W2:

Row\Col
A​
B​
C​
D​
E​
F​
G​
1​
T
W1
W2
Offset
2​
3.4950​
0.0031​
0.0020​
0​
0.825​
F2: =CORREL($B$2:$B$151, INDEX($C$2:$C$191, E2 + 1):INDEX($C$2:$C$191, E2 + 150))
3​
3.4954​
0.0033​
0.0019​
1​
0.856​
4​
3.4958​
0.0034​
0.0019​
2​
0.882​
5​
3.4963​
0.0035​
0.0018​
3​
0.902​
6​
3.4967​
0.0036​
0.0017​
4​
0.916​
7​
3.4971​
0.0037​
0.0017​
5​
0.923
8​
3.4975​
0.0037​
0.0017​
6​
0.921​
9​
3.4979​
0.0037​
0.0017​
7​
0.910​
10​
3.4983​
0.0036​
0.0017​
8​
0.890​
11​
3.4988​
0.0034​
0.0018​
9​
0.859​
12​
3.4992​
0.0032​
0.0019​
10​
0.819​
13​
3.4996​
0.0030​
0.0020​
11​
0.769​
14​
3.5000​
0.0027​
0.0020​
12​
0.710​
15​
3.5004​
0.0025​
0.0021​
13​
0.644​
16​
3.5008​
0.0024​
0.0021​
14​
0.570​
17​
3.5013​
0.0022​
0.0020​
15​
0.490​
18​
3.5017​
0.0021​
0.0019​
16​
0.405​
19​
3.5021​
0.0020​
0.0018​
17​
0.317​
20​
3.5025​
0.0019​
0.0016​
18​
0.226​
21​
3.5029​
0.0017​
0.0014​
19​
0.135​
22​
3.5033​
0.0015​
0.0011​
20​
0.044​
23​
3.5038​
0.0012​
0.0008​
21​
-0.045​
24​
3.5042​
0.0009​
0.0005​
22​
-0.130​
25​
3.5046​
0.0006​
0.0004​
26​
3.5050​
0.0002​
0.0002​
27​
3.5054​
-0.0003​
0.0000​
28​
3.5058​
-0.0008​
-0.0002​
29​
3.5063​
-0.0015​
-0.0004​
30​
3.5067​
-0.0021​
-0.0007​
31​
3.5071​
-0.0027​
-0.0009​
32​
3.5075​
-0.0033​
-0.0012​
33​
3.5079​
-0.0038​
-0.0014​
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,752
Office Version
2010
Platform
Windows
EDIT: For the first set of data, which has smaller time values, the correlation peak is at 0 offset. Maybe a better test is to see what time dilation of W1 is a best fit for W2. Does that make sense?
 

MrSak87

New Member
Joined
Jan 8, 2015
Messages
44
shg, I've not tried this yet but this is EXACTLY what I'm looking for!! Makes perfect sense I'll get back to you
 

MrSak87

New Member
Joined
Jan 8, 2015
Messages
44
Works perfectly! Thank you, you've no idea how much time I've spent trying to figure this out.

What do you mean by time dilation? Do you mean the amount of time change between the two waves as if one was stretched? If so how would I go about this?
 

Watch MrExcel Video

Forum statistics

Threads
1,101,946
Messages
5,483,831
Members
407,415
Latest member
Anton1999

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top