Using Var.S function to include the count of a value

[dchubbock]

Hello,

I would like to use the var.s function to calculate the variance between a set of numbers that factors in the COUNT of that number too.

Below is an example of the issue I am trying to solve. There is a two column table: one with the count, the other with the value. I want the var.s function to be able to include the count of the value; i have simulated how this would be by duplicating the numbers based on the count.

Var problem.jpg

It's the second var.s output that i'd like to achieve.

Apologies in advance if there is a super easy way to do this - i just can't see it!

[MrShorty]

If you want to use the var.s() function, the way you are doing it in C8 is probably the easiest.

When I want to calculate a weighted variance like that, I tend to abandon the built in function and go back to the basic formulas for variance -- specifically, this formula for the sum of the squared deviations: http://statistics.about.com/od/Formu...a-Shortcut.htm

var.s=sum((xi-ave(x))^2)/(n-1) where sum((xi-ave(x))^2)=sum(xi^2)-(sum(xi))^2/n

One can use the SUMPRODUCT() function to get the summations: http://office.microsoft.com/en-us/ex...in=HP010342656