Home / Statistical Tools / Analysis Tools / Time Series / How-To / Cross Correlation
Cross Correlation¶
Cross-correlation slides one series past another and measures the match at each offset. It is the one Time Series analysis that needs two columns.
Goal¶
Find how many periods separate a change in furnace temperature from the change it produces in downstream yield.
Sample Data¶
Twenty hourly readings of each. Press Copy for Excel, then paste into a blank worksheet.
| Furnace Temp | Yield |
|---|---|
| 97.4 | 80.0 |
| 98.0 | 80.9 |
| 102.2 | 79.3 |
| 101.5 | 77.3 |
| 105.7 | 82.1 |
| 107.3 | 82.9 |
| 92.3 | 81.0 |
| 99.4 | 84.0 |
| 109.1 | 75.1 |
| 101.8 | 81.8 |
| 103.5 | 87.2 |
| 94.7 | 80.8 |
| 100.6 | 83.0 |
| 106.1 | 74.6 |
| 97.2 | 79.7 |
| 99.0 | 84.4 |
| 93.1 | 77.9 |
| 94.4 | 78.3 |
| 89.3 | 75.4 |
| 95.6 | 77.8 |
Steps¶
-
Put the data in Excel
Press Copy for Excel, click cell A1 in a blank worksheet, and press Ctrl+V. Headers land in row 1 and the twenty pairs in rows 2 through 21.
-
Select the range first
Select cells A1:B21 before launching. This analysis needs two columns. You can also correct the range in the Data Selection window after launching.
-
Launch the analysis
From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Time Series → Cross Correlation.
-
Configure the analysis
Tick both Furnace Temp and Yield under Data Columns. On the Correlation Options tab, select Manual and set Lag Value: to 5.
Manual is worth using here. With twenty rows, Auto would compute fourteen lags in each direction, and the outermost of those rest on a handful of overlapping pairs.
Click Finish.
Result¶
A worksheet named CCF for followed by your source sheet name, since two columns were selected rather than one. The table is headed Cross-Correlation Function and has two columns, Lag and CCF. Negative and positive lags are both computed.
| Lag | CCF |
|---|---|
| -5 | -0.0727 |
| -4 | 0.0344 |
| -3 | 0.4744 |
| -2 | -0.1044 |
| -1 | 0.0962 |
| 0 | 0.1355 |
| 1 | 0.1742 |
| 2 | 0.8966 |
| 3 | -0.0423 |
| 4 | -0.2900 |
| 5 | -0.0059 |
Lag 2 stands out at 0.8966. The next largest anywhere in the table is 0.4744, at lag -3, barely more than half of it. The needle chart shows one tall spike among short ones.
Read the lag as an offset applied to the second column. A peak at lag +2 means that Yield matched against Furnace Temp from two rows earlier: a temperature change shows up in yield two hours later. Had the peak fallen at -2, the relationship would run the other way.
At lag 0 the two columns barely relate at all, 0.1355. Comparing the two columns row by row, which is what a plain correlation does, would have found almost nothing here. The whole value of this analysis is that it looks at the offsets as well.
What This Chart Does Not Have¶
- No confidence bands. The autocorrelation and partial autocorrelation charts both draw them; this one does not draw any, so there is no line here to be "outside".
- No accuracy measures, no equation and no forecast. Those belong to the three smoothing analyses.
- No legend on a single-pair chart. The needle plot carries one series, so the legend is hidden.
The ribbon spells this analysis Cross Correlation without a hyphen, while the worksheet heading, the chart title and the tab prefix all spell it Cross-Correlation. Both refer to the same thing.
Things to try next¶
- Select three or more columns. Every pairing is computed, not just neighbouring ones: three columns give three results, A against B, A against C, and B against C.
- Switch to Auto. Fourteen lags each way appear, out to ±14 on a twenty-row series. The far lags overlap on very few pairs and move a great deal for very little reason.
- Select only one column and try to launch. The Data Selection window will not go forward, and reports (Data in Columns) You must select 2 or more columns for this analysis. It is the only Time Series entry with that requirement.
A cross-correlation here can exceed 1
Each lag divides by the number of pairs that actually overlapped at that lag, multiplied by the two standard deviations of the whole series. Those two counts are not the same thing, so at large offsets a value slightly beyond the usual -1 to 1 range is possible. It is a property of the formula, not a fault in your data. Nothing in this walkthrough gets near it.