I'm trying to get descriptive statistics (mean, standard deviation, variance) from data of values between 1 and 7, which need to be weighted. I've used the following formula for the mean:

=SUMPRODUCT($A:$A,$B:$B)/(SUM($B:$B)-1)

where A:A is the column with the unweighted data, and B:B is the column with the weighting factor. This appears to work fine, and is giving out sensible numbers.

I've then applied this formula to get the variance:

=SUMPRODUCT($A2:$A3000,($B2:$B3000-AH10)^2)/(SUM($B2:$B3000)-1)

where AH10 is where the mean is stored.

However, this gives me a variance of 53!

I did think this could be due to the excess numbers in the variance formula (I only applied up to 3000 because it returned #VALUE when I used the columns, the number of values is actually 263), but altering the variance formula to (X)2: (X)263 doesn't make any difference.

Can anyone tell me where this formula is playing up?