# 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
Microsoft ExcelMath / Science

Last Comment
Steve
TommySzalapski

The square of the population standard deviation (Excel STDEVP function) is the variance.
=POWER(STDEVP(A:A),2)
Steve

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
morinia

The_Barman,

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

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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY