Browse » Home
Wednesday, February 15, 2012
How to Use Excel for Correlation of Data From Three Sensors
1. Arrange the data from the three sensors into columns in the Excel spreadsheet. To illustrate how to find the correlation, dummy numbers will be used as an example.
2. In column A, in cells A1 to A10, enter the following numbers:
0.33
0.05
0.77
0.90
0.25
0.66
0.30
0.12
0.05
0.85
3. In column B, in cells B1 to B10, enter the following numbers:
0.54
0.18
0.29
0.01
0.60
0.05
0.01
0.89
0.02
0.36
4. In column C, in cells C1 to C10, enter these numbers:
0.32
0.95
0.50
0.52
0.91
0.75
0.83
0.01
0.11
0.15
5. Make sure the Data Analysis add-in is switched on. Go to the 'Tools' setting on the menu bar, click 'Add Ins,' then check the box next to 'Analysis ToolPak.'
6. Click on the 'Tools' menu selection again, then scroll down to 'Data Analysis.'
7. Click on 'Data Analysis,' scroll down to 'Regression,' then click 'OK.'
8. Put the cursor in 'Input Y Range' then shade cells A1 to A10.
9. Put the cursor in 'Input X Range' then shade cells B1 to C10.
10. Click on 'OK' and the spreadsheet will produce a regression output data table automatically.
11. Look under regression statistics. The third number down is called 'Adjusted R Square.' This number represents the correlation of these three columns of data, which in this case are slightly negatively correlated with a value of -.16.