# Thread: Significance test between proportions (Excel xp)

1. ## 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

2. ## 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" 0-102, or =41-61 or =50-52, or even =50.999999-51.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(Avg1-Avg2))/SQRT(((NumSamples1-1)*Var1+(NumSamples2-1)*Var2)/(NumSamples1+NumSamples2-2)*(1/NumSamples1+1/NumSamples2))>TINV(alpha,NumSamples1+NumSamples2-2),"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

3. ## 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

4. ## Re: Significance test between proportions (Excel xp)

for comparing sample proportions with large sample sizes (> 20 pts):

Chi-sqr value for 90% (and 1 deg Freedom (comparing 2 proportions) = 2.71

Calc Chi-Sqr value = (N' * abs(PropA - ProbB) - 0.5)^2 / N' / P / (1-P)

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 Chi-Sqr > 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.

5. ## 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

6. ## Re: Significance test between proportions (Excel xp)

Good reference!

This section ("Basic statistics" - "Crosstabulation and stub-and-banner tables"):
http://www.statsoft.com/textbook/stbasic.h...Crosstabulation and stub-and-banner 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•