Results 1 to 6 of 6

20030625, 02:47 #1
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Significance test between proportions (Excel xp)
I have the attached spreadsheet. I want to put the formulas in COL E, G, I, K and M to add the stat letters next to the percentages. See E11. I put a B there since 51 is sig different than 60 (i just guessed it was). How would i get started on something like this? thank you

20030625, 11:19 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Significance test between proportions (Excel xp)
51 is NOT neccessarily significant different than 60!
I also think your workbook is missing some needed data (or I just can't find it!)
When comparing means for significance you need 7 pieces of info: (3 related to each number and one overall) and the and I can't seem to find all of them in your example:
1) Average of sample 1 and sample 2 (I assume this is the 51 and 60, you want to compare )
2) Number of samples used to determine the average. I will speculate that it is the the value in D6 (117, for 51) and in F6 (240, for 60)
3) Varaince of sample 1 and variance of sample 2. (Variance = StandardDeviation^2) What is the spread of the data? I found no information which can help tell me if 51 "is equal to" 0102, or =4161 or =5052, or even =50.99999951.999999. This is CRITICAL for determining "significance"
The last piece of information:
4) what is the significance level = alpha value, probability of "Type I error" (rejecting the hypothesis when it is true).
Typically one uses 0.05 ("95% Confidence"), but sometimes one might use 0.10 ("90%) or even 0.01 ("99%"), The smaller the value of alpha, the more likely you call them the SAME.
The equation you can use is (all one line)
=IF((ABS(Avg1Avg2))/SQRT(((NumSamples11)*Var1+(NumSamples21)*Var2)/(NumSamples1+NumSamples22)*(1/NumSamples1+1/NumSamples2))>TINV(alpha,NumSamples1+NumSamples22),"Different", "Same")
If you put this in E11 and use the appropriate relative and absolute references for the values, you can copy the formula to other cells to get the output. You can change the "Different" and the "Same" to "B" and something else (null string?) if desired.
Hope this helps,
Steve

20030625, 12:30 #3
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Significance test between proportions (Excel xp)
Thank you so much for the explanation.
These numbers are percentages so i need signif diff between 2 proportions not means. The confidence level is 90%. I have to test A to B, B to A, A to C, C to A, B to C, C to B etc. Can i do all this in a formula or do i have to use a macro? thanks

20030625, 13:57 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Significance test between proportions (Excel xp)
for comparing sample proportions with large sample sizes (> 20 pts):
Chisqr value for 90% (and 1 deg Freedom (comparing 2 proportions) = 2.71
Calc ChiSqr value = (N' * abs(PropA  ProbB)  0.5)^2 / N' / P / (1P)
N' = NumA * NumB /(NumA + NumB)
P = (PropA*NumA + PropB* NumB) / (NumA + NumB)
PropA = Proportion of A
PropB = Proportion of B
NumA = Number of points of A
NumB = Number of points of B
You can use intermediate formulas combine in a mega formula or create a UDF.
If the calc ChiSqr > 2.71 than they are different
Steve
PS you might want to invest in a Statistics Book, we are getting way out of the "excel Help" here! Check your library, or google it.

20030626, 06:21 #5
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Significance test between proportions (Excel xp)
Hi Steve,
I find the online electronic stats book by Statsoft very helpful:
http://www.statsoft.com/textbook/stathome.html
It used to be downloadable too, couldn't find how though.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030626, 08:22 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Significance test between proportions (Excel xp)
Good reference!
This section ("Basic statistics"  "Crosstabulation and stubandbanner tables"):
http://www.statsoft.com/textbook/stbasic.h...Crosstabulation and stubandbanner tables
seems to be related to JHA900's data, though I am NOT completely sure what the data represents, which is why I have been giving more "general answers" to the questions and NOT excel answers specific to his data.
I feel uncomfortable giving a specific calc method, when I do NOT know if it is valid technique.
Steve