Odd results from SUMPRODUCT variance formula
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? |
All times are GMT +1. The time now is 11:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com