JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Cross Correlation analysis with two series of wavelets
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
Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
Seconds x1 x2
0 -5.87E-05 -1.00E-04 0.000417 -1.05E-05 -2.16E-05 0.000833 4.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.0025 2.38E-06 -9.34E-06 0.002917 2.43E-05 2.88E-05 0.003333 2.85E-05 4.73E-05 0.00375 2.64E-05 4.24E-05 0.004167 3.66E-05 5.96E-05 0.004583 3.98E-05 7.17E-05 0.005 4.37E-05 7.63E-05 0.005417 5.28E-05 8.52E-05 0.005833 5.39E-05 8.75E-05 0.00625 5.18E-05 8.56E-05 0.006667 5.28E-05 8.54E-05 0.007083 5.40E-05 9.17E-05 0.0075 5.90E-05 9.97E-05 0.007917 1.08E-04 1.60E-04 0.008333 1.45E-04 1.99E-04 0.00875 1.39E-04 1.99E-04 0.009167 1.36E-04 1.80E-04 0.009583 1.30E-04 1.80E-04 0.01 1.16E-04 1.54E-04 0.010417 6.84E-05 7.92E-05 0.010833 6.07E-05 6.89E-05 0.01125 6.62E-05 8.04E-05 0.011667 6.45E-05 7.60E-05 0.012083 6.16E-05 7.48E-05 0.0125 5.05E-05 5.67E-05 0.012917 2.93E-05 2.13E-05 0.013333 1.75E-05 -2.09E-06 0.01375 2.20E-05 2.43E-06 0.014167 8.96E-06 -1.58E-05 0.014583 1.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-05 1.71E-06 0.023333 -1.95E-06 3.21E-05 0.02375 -5.21E-06 2.40E-05 0.024167 7.12E-06 3.62E-05 0.024583 1.91E-05 5.31E-05 0.025 2.52E-05 5.70E-05 0.025417 3.56E-05 6.15E-05 0.025833 3.97E-05 5.74E-05 0.02625 3.76E-05 5.69E-05 0.026667 4.27E-05 5.82E-05 0.027083 5.11E-05 6.80E-05 0.0275 5.87E-05 7.57E-05 0.027917 1.05E-04 1.29E-04 0.028333 1.49E-04 1.81E-04 0.02875 1.41E-04 1.70E-04 0.029167 1.40E-04 1.70E-04 0.029583 1.41E-04 1.66E-04 0.03 1.27E-04 1.43E-04 0.030417 9.39E-05 8.50E-05 0.030833 8.17E-05 7.44E-05 0.03125 8.67E-05 8.38E-05 0.031667 8.48E-05 8.61E-05 0.032083 8.74E-05 8.60E-05 0.0325 8.33E-05 7.90E-05 0.032917 7.50E-05 5.40E-05 0.033333 6.36E-05 2.61E-05 0.03375 6.23E-05 3.00E-05 0.034167 5.24E-05 1.50E-05 0.034583 4.81E-05 2.19E-06 0.035 3.70E-05 -2.96E-06 0.035417 3.02E-05 -5.15E-06 0.035833 2.32E-05 -3.19E-06 0.03625 2.56E-05 3.55E-07 0.036667 2.18E-05 1.55E-06 0.037083 1.80E-05 -2.66E-06 0.0375 1.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-06 1.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-06 4.14E-06 0.042917 6.25E-08 2.92E-05 0.043333 7.30E-06 5.10E-05 0.04375 8.75E-06 4.48E-05 0.044167 1.70E-05 5.52E-05 0.044583 1.98E-05 6.75E-05 0.045 2.63E-05 7.14E-05 0.045417 3.58E-05 7.56E-05 0.045833 3.74E-05 6.91E-05 0.04625 3.74E-05 6.47E-05 0.046667 4.09E-05 6.02E-05 0.047083 5.32E-05 6.52E-05 0.0475 6.87E-05 7.24E-05 0.047917 1.31E-04 1.35E-04 0.048333 1.76E-04 1.76E-04 0.04875 1.72E-04 1.63E-04 0.049167 1.68E-04 1.61E-04 0.049583 1.57E-04 1.52E-04 0.05 1.37E-04 1.33E-04 0.050417 9.06E-05 7.19E-05 0.050833 6.81E-05 6.91E-05 0.05125 6.81E-05 9.16E-05 0.051667 6.27E-05 1.04E-04 0.052083 5.57E-05 1.10E-04 0.0525 3.63E-05 8.89E-05 0.052917 1.58E-05 4.87E-05 0.053333 8.46E-06 1.64E-05 0.05375 1.13E-05 1.98E-05 0.054167 1.13E-05 1.71E-06 0.054583 2.28E-05 -1.23E-05 0.055 2.99E-05 -1.81E-05 0.055417 3.27E-05 -2.28E-05 0.055833 3.74E-05 -2.92E-05 0.05625 4.59E-05 -3.58E-05 0.056667 5.21E-05 -3.75E-05 0.057083 5.88E-05 -4.37E-05 0.0575 6.03E-05 -4.56E-05 0.057917 1.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-05 2.29E-05 0.060833 -9.01E-05 4.24E-05 0.06125 -1.03E-04 3.17E-05 0.061667 -1.12E-04 2.52E-05 0.062083 -1.21E-04 8.89E-06 0.0625 -1.24E-04 -7.00E-06 0.062917 -1.11E-04 3.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.0675 2.99E-06 6.24E-06 0.067917 5.31E-05 8.11E-05 0.068333 1.01E-04 1.45E-04 0.06875 9.30E-05 1.41E-04 0.069167 9.13E-05 1.42E-04 0.069583 8.50E-05 1.26E-04 0.07 7.06E-05 1.03E-04 0.070417 3.97E-05 3.95E-05 0.070833 3.97E-05 3.43E-05 0.07125 5.80E-05 5.52E-05 0.071667 7.04E-05 5.92E-05 0.072083 7.43E-05 5.59E-05 0.0725 7.38E-05 3.49E-05 0.072917 5.84E-05 -8.99E-06 0.073333 6.91E-05 -2.55E-05 0.07375 8.75E-05 -1.27E-05 0.074167 9.86E-05 -1.90E-05 0.074583 1.09E-04 -2.13E-05 0.075 1.20E-04 -1.69E-05 0.075417 1.20E-04 -1.35E-05 0.075833 1.20E-04 -5.45E-06 0.07625 1.27E-04 3.02E-06 0.076667 1.30E-04 9.92E-06 0.077083 1.30E-04 1.34E-05 0.0775 1.27E-04 2.26E-05 0.077917 7.65E-05 -2.46E-05 0.078333 3.20E-05 -5.72E-05 0.07875 2.71E-05 -3.99E-05 0.079167 8.17E-06 -3.56E-05 0.079583 -1.37E-07 -2.47E-05 0.08 5.73E-06 4.25E-06 0.080417 4.17E-05 7.24E-05 0.080833 5.02E-05 8.53E-05 0.08125 3.78E-05 7.21E-05 0.081667 2.81E-05 6.80E-05 0.082083 1.36E-05 6.02E-05 0.0825 5.94E-06 6.35E-05 0.082917 9.02E-06 9.83E-05 0.083333 2.33E-06 1.13E-04 0.08375 -2.20E-06 9.83E-05 0.084167 3.62E-06 1.08E-04 0.084583 3.91E-06 1.11E-04 0.085 3.76E-06 1.08E-04 0.085417 1.52E-06 9.89E-05 0.085833 -7.79E-06 8.30E-05 0.08625 -2.08E-05 6.92E-05 0.086667 -2.47E-05 5.99E-05 0.087083 -3.02E-05 6.28E-05 0.0875 -2.91E-05 6.58E-05 0.087917 1.53E-05 1.19E-04 0.088333 5.31E-05 1.60E-04 0.08875 3.94E-05 1.31E-04 0.089167 3.74E-05 1.21E-04 0.089583 3.74E-05 9.89E-05 0.09 3.10E-05 6.90E-05 0.090417 1.12E-06 -6.51E-06 0.090833 7.02E-06 -1.85E-05 0.09125 2.86E-05 -3.42E-06 0.091667 4.07E-05 3.16E-07 0.092083 5.32E-05 7.17E-08 0.0925 5.77E-05 -1.32E-05 0.092917 4.67E-05 -4.77E-05 0.093333 5.19E-05 -5.85E-05 0.09375 6.39E-05 -4.37E-05 0.094167 6.50E-05 -4.44E-05 0.094583 6.47E-05 -4.77E-05 0.095 5.75E-05 -3.81E-05 0.095417 4.84E-05 -3.27E-05 0.095833 4.23E-05 -1.81E-05 0.09625 4.97E-05 -3.87E-06 0.096667 5.77E-05 1.04E-05 0.097083 6.76E-05 1.27E-05 0.0975 7.43E-05 1.14E-05 0.097917 3.52E-05 -4.99E-05 0.098333 1.42E-05 -9.43E-05 0.09875 3.72E-05 -8.13E-05 0.099167 5.16E-05 -8.16E-05 0.099583 6.85E-05 -6.28E-05 0.1 8.66E-05 -3.01E-05
<colgroup><col><col><col></colgroup><tbody>
</tbody>
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
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.495 0.003115 0.001985 3.495417 0.003294 0.001946 3.495833 0.003429 0.001883 3.49625 0.003548 0.001806 3.496667 0.003639 0.00173 3.497083 0.003709 0.001679 3.4975 0.00374 0.001686 3.497917 0.003703 0.001711 3.498333 0.003556 0.001727 3.49875 0.003412 0.00184 3.499167 0.003209 0.00191 3.499583 0.002988 0.00197 3.5 0.002736 0.002007 3.500417 0.002539 0.002056 3.500833 0.00238 0.002056 3.50125 0.002244 0.001977 3.501667 0.002132 0.001892 3.502083 0.002012 0.001765 3.5025 0.001865 0.001573 3.502917 0.001694 0.001352 3.503333 0.001466 0.00108 3.50375 0.001204 0.000792 3.504167 0.000926 0.000546 3.504583 0.000611 0.000363 3.505 0.000216 0.000193 3.505417 -0.00027 1.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.517084 0.000113 -0.00245 3.5175 0.000305 -0.00205 3.517917 0.000438 -0.00168 3.518334 0.000519 -0.00134 3.51875 0.000652 -0.00089 3.519167 0.000708 -0.00047 3.519584 0.000729 -3.4E-05 3.52 0.000687 0.000389 3.520417 0.000608 0.000794 3.520834 0.000495 0.001104 3.52125 0.000324 0.001285 3.521667 9.63E-05 0.001408 3.522084 -0.00016 0.001494 3.5225 -0.00041 0.00155 3.522917 -0.00059 0.001613 3.523334 -0.00074 0.001605 3.52375 -0.0009 0.001497 3.524167 -0.00104 0.001325 3.524584 -0.00112 0.001144 3.525 -0.00117 0.000966 3.525417 -0.00117 0.000823 3.525834 -0.00108 0.000694 3.52625 -0.00091 0.000594 3.526667 -0.0007 0.000532 3.527084 -0.00048 0.000521 3.5275 -0.00025 0.000531 3.527917 7.3E-06 0.000578 3.528334 0.000349 0.000716 3.52875 0.00063 0.000816 3.529167 0.000928 0.001012 3.529584 0.0012 0.001246 3.53 0.001466 0.001472 3.530417 0.00167 0.001611 3.530834 0.001849 0.001731 3.53125 0.002003 0.001878 3.531667 0.002096 0.001979 3.532084 0.002182 0.002091 3.5325 0.002285 0.002206 3.532917 0.002406 0.002279 3.533334 0.002551 0.002348 3.53375 0.002706 0.002393 3.534167 0.002852 0.002379 3.534584 0.002982 0.002315 3.535 0.003103 0.002254 3.535417 0.003205 0.002189 3.535834 0.003291 0.002122 3.53625 0.003371 0.002048 3.536667 0.003442 0.00195 3.537084 0.003479 0.001838 3.5375 0.003489 0.001761 3.537917 0.003459 0.001702 3.538334 0.003374 0.001643 3.53875 0.003342 0.00171 3.539167 0.003263 0.001756 3.539584 0.003165 0.001798 3.54 0.003017 0.001825 3.540417 0.002874 0.001881 3.540834 0.002682 0.001888 3.54125 0.002449 0.001827 3.541667 0.00222 0.00178 3.542084 0.001999 0.001719 3.5425 0.001765 0.001628 3.542917 0.001566 0.001529 3.543334 0.00135 0.001367 3.54375 0.001121 0.001136 3.544167 0.00086 0.000885 3.544584 0.000536 0.000623 3.545 0.000138 0.000342 3.545417 -0.00029 6.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.5575 8.75E-05 -0.00215 3.557917 0.000319 -0.00176 3.558334 0.000493 -0.00143 3.55875 0.000688 -0.00102 3.559167 0.000791 -0.00064 3.559584 0.000846 -0.00024 3.56 0.000862 0.000145 3.560417 0.000833 0.000543 3.560834 0.000738 0.000879 3.56125 0.00056 0.001115 3.561667 0.000355 0.001324 3.562084 0.000147 0.001476 3.5625 -7.3E-05 0.001586 3.562917 -0.00027 0.001671 3.563334 -0.00046 0.001679 3.56375 -0.00065 0.0016 3.564167 -0.00079 0.001485 3.564584 -0.00087 0.00136 3.565 -0.00092 0.001206 3.565417 -0.00092 0.001057 3.565834 -0.00089 0.000903 3.56625 -0.00085 0.000769 3.566667 -0.00079 0.000657 3.567084 -0.00072 0.000595 3.5675 -0.00064 0.000576 3.567917 -0.00049 0.000638 3.568334 -0.00021 0.000808 3.56875 4.79E-05 0.000932 3.569167 0.000385 0.001122 3.569584 0.00077 0.001288 3.57 0.001197 0.001422 3.570417 0.001565 0.001463 3.570834 0.001884 0.001503 3.57125 0.002141 0.001604 3.571667 0.002322 0.001716 3.572084 0.002467 0.001869 3.5725 0.002583 0.002053 3.572917 0.00265 0.002205 3.573334 0.002726 0.002355 3.57375 0.002815 0.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
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
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
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?
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
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?
Threads
1,214,541
Messages
6,120,110
Members
448,945
Latest member
Vmanchoppy