Question

please show me what hand written function to use and exactly how to use it. thanks!

By formula By Function 2.89 0.974632693 WN relation is the one between the res of year and The National Safety Council of the U.S. released the following data on the incidence rates for fatal or lost-worktime injuries per 100 employees for several industries in three recent years. Computer for each pair of years and determine which years are most highly correlated. Calculater using 1. the formula and 2. function.

this question is already posted but does not explain which formula you use and how to use it

Answer 1

Given,

year 1	year 2	year 3
0.46	0.48	0.69
0.52	0.62	0.63
0.9	0.72	0.81
1.5	1.74	2.1
2.89	2.03	2.46
1.8	1.92	2
3.29	3.18	3.17
5.73	4.43	4

r1 : Correlation between year1: x and year2: y

ηΣχy – ΣΥΣ [nΣ. 2 - (Σκ)21 [nΣ2 - (Σο)21

n : Number of observations:8

	year 1:x	year 2:y	xy	x2	y2
	0.46	0.48	0.2208	0.2116	0.2304
	0.52	0.62	0.3224	0.2704	0.3844
	0.9	0.72	0.648	0.81	0.5184
	1.5	1.74	2.61	2.25	3.0276
	2.89	2.03	5.8667	8.3521	4.1209
	1.8	1.92	3.456	3.24	3.6864
	3.29	3.18	10.4622	10.8241	10.1124
	5.73	4.43	25.3839	32.8329	19.6249
Total	$\small \Sigma x$=17.09	$\small \Sigma y$=15.12	$\small \Sigma xy$=48.97	$\small \Sigma x^{2}$=58.7911	$\small \Sigma y^{2}$=41.7054

ηΣχy – ΣΥΣ [nΣ. 2 - (Σκ)21 [nΣ2 - (Σο)21

$\small \\=\frac{8\times48.97-17.09\times15.12}{\sqrt{ \left [8\times58.7911 -(17.09)^{2}\right ]\left [8\times41.7054-(15.12)^{2} \right ]}}\\\\\\=\frac{391.76-258.4008}{\sqrt{ \left [470.3288 -292.0681\right ]\left [333.6432-228.6144 \right ]}}$

$\small =\frac{133.3592}{\sqrt{ 18722.50740816 }}=0.97463269$

----------------------------------------------------------------------

r2: Correlation between year2: x and year3: y

$\small r2=\frac{n\Sigma xy-\Sigma x\Sigma y}{\sqrt{ \left [ n\Sigma x^{2} -(\Sigma x)^{2}\right ]\left [ n\Sigma y^{2}-(\Sigma y)^{2} \right ]}}$

	year 2:x	year 3:y	xy	x2	y2
	0.48	0.69	0.3312	0.2304	0.4761
	0.62	0.63	0.3906	0.3844	0.3969
	0.72	0.81	0.5832	0.5184	0.6561
	1.74	2.1	3.654	3.0276	4.41
	2.03	2.46	4.9938	4.1209	6.0516
	1.92	2	3.84	3.6864	4
	3.18	3.17	10.0806	10.1124	10.0489
	4.43	4	17.72	19.6249	16
Total	$\small \Sigma x$=15.12	$\small \Sigma y$=15.86	$\small \Sigma xy$=41.5934	$\small \Sigma x^{2}$=41.7054	$\small \Sigma y^{2}$=42.0396

$\small r2=\frac{n\Sigma xy-\Sigma x\Sigma y}{\sqrt{ \left [ n\Sigma x^{2} -(\Sigma x)^{2}\right ]\left [ n\Sigma y^{2}-(\Sigma y)^{2} \right ]}}$

$\small =\frac{8\times41.5934-15.12\times15.86}{\sqrt{ \left [8\times41.7054 -(15.12)^{2}\right ]\left [8\times42.0396-(15.86)^{2} \right ]}} \\\\\\=\frac{332.7472-239.8032}{\sqrt{ \left [333.6432 -228.6144\right ]\left [336.3168-251.5396 \right ]}} \\\\\\=\frac{92.944}{\sqrt{ 8904.04758336 }} =0.98498048$

------------------------------------------

r3 : Correlation between year1: x and year3: y

$\small r3=\frac{n\Sigma xy-\Sigma x\Sigma y}{\sqrt{ \left [ n\Sigma x^{2} -(\Sigma x)^{2}\right ]\left [ n\Sigma y^{2}-(\Sigma y)^{2} \right ]}}$

	year 1:x	year 3	xy	x2	y2
	0.46	0.69	0.3174	0.2116	0.4761
	0.52	0.63	0.3276	0.2704	0.3969
	0.9	0.81	0.729	0.81	0.6561
	1.5	2.1	3.15	2.25	4.41
	2.89	2.46	7.1094	8.3521	6.0516
	1.8	2	3.6	3.24	4
	3.29	3.17	10.4293	10.8241	10.0489
	5.73	4	22.92	32.8329	16
Total	$\small \Sigma x$=17.09	$\small \Sigma y$=15.86	$\small \Sigma xy$=48.5827	$\small \Sigma x^{2}$=58.7911	$\small \Sigma y^{2}$=42.0396

$\small =\frac{8\times48.5827-17.09\times15.86}{\sqrt{ \left [8\times58.7911 -(17.09)^{2}\right ]\left [8\times42.0396-(15.86)^{2} \right ]}} \\\\\\=\frac{388.6616-271.0474}{\sqrt{ \left [470.3288 -292.0681\right ]\left [336.3168-251.5396 \right ]}} \\\\\\=\frac{117.6142}{\sqrt{ 15112.44301604 }} =0.95673667$

---------------------------------------------

Using Excel function Correl

AutoSave Of H b c D 2 File Home Insert Draw Page Layout Formulas Data Review View = -11 Calibri À À = = ambril Paste G B I U - - - A- Clipboard Font Alignment year 1 Animo year 2 0.46 0.52 0.9 1.5 2.89 1.8 3.29 5.73 year 3 0.48 0.62 0.72 1.74 2.03 1.92 3.18 4.43 0.69 0.63 0.81 2.1 2.46 2 3.17 4 1 Correlation between r1: Year 1 and Year 2 r2: Year 2 and Year 3 r3: Year 1 and Year 3 2 Value 0.97463269 0.98498048 0.95673667 3 Formula CORREL(A1:A9,B1:B9) CORREL(B1:B9,C1:09) CORREL(A1:A9,C1:09)

		By formula	By function
r1	Year 1 and Year 2	0.97463269	0.97463269
r2	Year 2 and Year 3	0.98498048	0.98498048
r3	Year 1 and Year 3	0.95673667	0.95673667

The highest correlation is between Year 2 and Year 3: which 0.98498048