morinia

asked on

# 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

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

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

The_Barman,

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

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

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.

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.

=POWER(STDEVP(A:A),2)