Scatter plots in Excel - Cell-by-Cell Formula Trace

Sample Data

This data shows heights in centimeters and weights in kilograms for 5 people.

Cell	Value
A1	Height (cm)
B1	Weight (kg)
A2	150
B2	50
A3	160
B3	60
A4	170
B4	65
A5	180
B5	70
A6	190
B6	80

Formula Trace

CORREL(A2:A6, B2:B6)

Step 1: A2:A6 values

Step 2: B2:B6 values

Step 3: Calculate correlation coefficient between heights and weights

Cell Reference Map

    A       B
1 Height  Weight
2  150  -->  50
3  160  -->  60
4  170  -->  65
5  180  -->  70
6  190  -->  80

Cells A2:A6 and B2:B6 are the data points used for the scatter plot and correlation calculation.

Result

    A       B       C
1 Height  Weight  Correlation
2  150     50       0.991
3  160     60
4  170     65
5  180     70
6  190     80

The correlation coefficient 0.991 is shown in cell C2, indicating a strong positive relationship between height and weight.

Sheet Trace Quiz - 3 Questions

What does the CORREL function calculate with the data in A2:A6 and B2:B6?

A The strength and direction of the relationship between height and weight

B The average height

C The total weight

D The difference between max and min height

Key Result

CORREL(range1, range2) calculates the correlation coefficient between two sets of numbers.