Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Abrupt change in formula results using sumproduct mbeemom Excel Worksheet Functions 4 January 8th 09 08:27 PM
What is results table mean for-t-TestTwoSample unequal variance Dilia Excel Discussion (Misc queries) 1 October 31st 08 05:05 PM
Pivot Tables - Variance and Variance % PJS Excel Discussion (Misc queries) 2 January 18th 06 04:12 AM
Percent Variance Formula mdalby Excel Discussion (Misc queries) 1 August 25th 05 05:07 PM
Pivot Tables - Variance and % Variance fields CraigS Excel Discussion (Misc queries) 5 January 6th 05 01:22 AM


All times are GMT +1. The time now is 04:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"