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:$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? 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 for-t-TestTwoSample 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) |