COVAR() from first principles

[Getafix1066]

Hi,

Can anyone point me in the direction of an COVAR() tutorial/example that shows how the COVAR() function is calculated?

I'm NOT looking for examples of how it's used, I'm looking for how it's CALCULATED using lower level functions i.e. SUM(), AVG(), COUNT(), multiplication and addition etc.

thanks

 

[DRSteele]

You can watch these videos and others in the Statistics realm on the ExcelIsFun channel on youtube.

https://www.youtube.com/watch?v=4uC4Ck4DCWs
https://www.youtube.com/watch?v=5qqY5fuU61c

 

[shg]

=sumproduct((x - average(x)) * (y - average) / count(x)

 

[AhoyNC]

A few more links.

https://www.youtube.com/watch?v=uzW9WKHzSYM
https://www.youtube.com/watch?v=xGbpuFNR1ME
https://www.youtube.com/watch?v=0nZT9fqr2MU

 

[Getafix1066]

Thanks for the responses, to clarify what I'm looking for. I'm trying to replicate the COVAR() function myself using an excel spreadsheet and basic excel functions, SUM(), AVG, COUNT() etc.

I've read the wikipedia article and seen some videos, but to fully understand it I want see the step by step working on a spreadsheet using low-level functions.

 

[AhoyNC]

Maybe this will help.
Based on the example in this video
https://www.youtube.com/watch?v=uzW9WKHzSYM

The example below breaks down each step in the video using Excel functions.
Plus using the formula that SHG gave you which puts it all in one formula.
NOTE: if this was a sample then n would be n-1 or 8 in this case.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1		Data
2	X	1	2	3	4	5	6	7	8	9
3	Y	5	4	5	6	8	9	10	13	12
4
5	Mean (average)
6	x	5
7	y	8
8
9	Square x	1	4	9	16	25	36	49	64	81
10	Square y	25	16	25	36	64	81	100	169	144
11
12	Sum of x^2	285		Var. of x	6.667
13	Sum of y^2	660		Var. of y	9.333
14
15	x*y	5	8	15	24	40	54	70	104	108
16
17	Sigma xy	428
18	n	9
19
20	cov =	7.556
21
22	EXCEL COV()	7.556
23
24	Formula from SHG	7.556
25
Sheet

 

[Getafix1066]

Thanks AhoyNc, just what I needed

 

[AhoyNC]

You're welcome. Gad that helped.