Home 
Search 
Today's Posts 
#1




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:$B3000AH10)^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? Last edited by JamesM1986 : April 6th 16 at 05:43 PM 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Abrupt change in formula results using sumproduct  Excel Worksheet Functions  
What is results table mean fortTestTwoSample unequal variance  Excel Discussion (Misc queries)  
Pivot Tables  Variance and Variance %  Excel Discussion (Misc queries)  
Percent Variance Formula  Excel Discussion (Misc queries)  
Pivot Tables  Variance and % Variance fields  Excel Discussion (Misc queries) 