Remember Me?

#1
April 6th 16, 05:05 PM
 Junior Member First recorded activity by ExcelBanter: Apr 2016 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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post mbeemom Excel Worksheet Functions 4 January 8th 09 07:27 PM Dilia Excel Discussion (Misc queries) 1 October 31st 08 04:05 PM PJS Excel Discussion (Misc queries) 2 January 18th 06 03:12 AM mdalby Excel Discussion (Misc queries) 1 August 25th 05 05:07 PM CraigS Excel Discussion (Misc queries) 5 January 6th 05 12:22 AM

All times are GMT +1. The time now is 11:01 PM.