caculating a variance in Excel


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:
This will give the Correlation Coeficient

This will give the Coeficient of determination
(the percentage variation in Y resulting from changes in X)
This is the variance.
The square of the population standard deviation (Excel STDEVP function) is the variance.
moriniaAdvanced Analytics AnalystAuthor Commented:

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

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.
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.