caculating a variance in Excel

Experts,

I have to columns of data.  One has the number of claims for a member.  The other has the number of months the member is enrolled.

i am trying to calculate the variance to see if there is a correlation between the two.  Does anyone have any idea how I would dot this in Excel.

Using ColumnA as number of claims
          ColumnB as number of months enrolled
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
SteveConnect With a Mentor Commented:
I think you should be looking at the Pearson Correlation Coeficient and the Coeficient of determination:

For example using data in A2:B99:
=PEARSON(A2:A99,B2:B99)
This will give the Correlation Coeficient

=PEARSON(A2:A99,B2:B99)^2
This will give the Coeficient of determination
(the percentage variation in Y resulting from changes in X)
This is the variance.
0
 
TommySzalapskiCommented:
The square of the population standard deviation (Excel STDEVP function) is the variance.
=POWER(STDEVP(A:A),2)
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
The_Barman,

If I receive a value of .348, does that mean there is little correlation?
0
 
SteveCommented:
correct.

Correlation Coeficient runs between 1 and -1 with 1 being perfect positive corelation and 0 being no correlation.
Correlation above .8 or below -.8 are good. Between .5 and -.5 is poor correlation.

As .348^2 (.348 squared) = 0.1211 = 12%
Only 12% of the change in the Y value can be attributed to variation in the X value.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.