Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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" 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. #3
    4 Star Lounger
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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):

    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. #5
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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 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
  •