Question

An amateur astronomer is researching statistical properties of known stars using a variety of databases. They collect the color index, or B−V index, and distance (in light years) from Earth for 30 stars. The color index of a star is the difference in the light absorption measured from the star using two different light filters (a B and a V filter). This then allows the scientist to know the star's temperature and a negative value means a hot blue star. A light year is the distance light can travel in 1 year, which is approximately 5.9 trillion miles. The data is provided below. Use Excel to calculate the correlation coefficient r between the two data sets, rounding to two decimal places.

B-V index   Distance (ly)
1.1   1380
0.4   556
1.0   771
0.5   304
1.4   532
1.0   751
0.5   267
0.8   229
0.5   552
0.2   896
1.5   1819
0.5   381
0.5   257
1.1   541
0.7   133
0.5   300
0.0   985
0.4   525
1.0   408
1.1   1367
1.07   2848
1.1   128.9
1.12   1766.2
0.64   186.5
0.87   8269.2
0.19   828.9
1.03   153
0.55   223.6
1.39   963.9
0.89   91.7

Answer 1

First I have cleaned the data in excel and then I have used CORREL function to get the correlation value

the data is pasted in tabular form below

B-V index	Distance
1.10	1380.00
0.40	556.00
1.00	771.00
0.50	304.00
1.40	532.00
1.00	751.00
0.50	267.00
0.80	229.00
0.50	552.00
0.20	896.00
1.50	1819.00
0.50	381.00
0.50	257.00
1.10	541.00
0.70	133.00
0.50	300.00
0.00	985.00
0.40	525.00
1.00	408.00
1.10	1367.00
1.07	2848.00
1.10	128.90
1.12	1766.20
0.64	186.50
0.87	8269.20
0.19	828.90
1.03	153.00
0.55	223.60
1.39	963.90
0.89	91.70

The correlation value I have found is

0.176881

which when rounded to 2 decimal places, yield 0.18

I have also plotted the graph for the standardised values

Which evidently signifies the same result.

Hope this answer has helped you. If you have some queries regarding this, do let me know in the comment section.

Hit like if the answer really helped you.

Thanks !!