Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sig Diff for proportions formula (Excel xp)

    I have a sheet with 3 columns. A has the national percentage, B has the regional percentage, and C has the individual store percentage. I need to compare C to A and C to B and do a Z test to see if they are significant to 90% confidence level. I have the sample sizes of each individual store. Is there a function that i can plug in the percentages and sample sizes and it will return a true or false whether it is significant or not? Does anyone have a sample i can study? thank you for the help.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Sig Diff for proportions formula (Excel xp)

    Hi jha,

    Does the attached help? You put sample sizes in B3 & B4, and observations in B5 & B6.

    Columns C-I were set up for scenario modelling

    Cheers

    PS: System gremlins got to my last attempt at posting this.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sig Diff for proportions formula (Excel xp)

    yes, thank you very much. So if my natl sample size is 900, and my individ sample size is 100, and my natl percentage x1=45%, and my indiv percentage is x2=60%, then it is signif different (Reject).

    Actually it rejects all my attempts unless my indiv percentage is x2=45%. am i doing something wrong?

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Sig Diff for proportions formula (Excel xp)

    Hi jha,

    The x values need to be numbers, not percentages, unless the n values are the same.

    So, if n1 is 900, x1 is 900*.45=405, n2 is 100 and x2 is 100*.6=60

    You'll find that the Accept/Reject responses for the above example will start changing when x2 is around 54+

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sig Diff for proportions formula (Excel xp)

    ok, thank you. I am still struggling with it tho. Do i just use COL B. I have entered...

    B3 900
    B4 100
    B5 .45
    B6 .6

    What cell shows me 90% and which is 95% confidence level? Also it IS significant if reject is in that cell--is that correct? thanks

  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: Sig Diff for proportions formula (Excel xp)

    Based on what macropod posted:
    90% is the

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Sig Diff for proportions formula (Excel xp)

    Hi jha,

    Following on from Steve's post, you need to have:

    B3 900
    B4 100
    B5 405
    B6 60

    As I said in my last post, you should use numbers, not percentages.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sig Diff for proportions formula (Excel xp)

    Thank you for the help. I am still struggling with this... please see the attached

    The following shows the percentages for males and females that shop at an individual store. COL B shows the percentages at for the store's region, and COL C is the national figures.

    I need to turn the individual store's cell yellow if there is a significant diff (at 90% confidence level) between the indiv store compared to the regional level. Also i need to bold the individ store's number if there is a signif diff between the indiv store compared to the national level.

    I have to do this for 40 individual stores and about 100 different attributes. I have the indiv store, regional, and national data linked and it will fall in COL A, B and C. I also have the samples sizes linked in COL D, E and F.

    I have the formulas from the workbook example you sent me but i don't know how to integrate the 2. I am at a deadend and need some help in getting further. Thank you for the help.

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Sig Diff for proportions formula (Excel xp)

    Hi jha,

    I see you're still using percentages instead of numbers. The solution I posted requires the input of the numbers for both the sample sizes and the observations. Since you seem committed to using percentages for the observations, I'll modify the formula to suit.

    Apart from that, it looks like you need a conditional formatting solution. Before I implement that, though, I note that you say that you want to modify the store's cell in one case and the store number's cell in the other. Am I correct to conclude from this that the store and store number are in different cells? They are not in the spreadsheet attached to your last post.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sig Diff for proportions formula (Excel xp)

    I can't thank you enough for the continued help! I'm sorry i was unclear. The store's cell and the store's number cell are the same. I just meant the number in the store's cell needs to be bolded. Does that make sense or am i being too wordy? <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Sig Diff for proportions formula (Excel xp)

    Hi jha,

    Attached is an update to the spreadsheet attached to your last post. As you'll see, I've modified the diff tests to work with percentages, and I've incorporated the results into a conditional format that mdifies the font and/or background to suit (I think I've interpreted your requirements correctly).

    Some explanation is in order. Four true/false conditions exist:
    0 the National:Local difference and the Regional:Local, difference are both not significant
    1 the National:Local difference is significant but the Regional:Local, difference is not significant
    2 the Regional:Local difference is significant but the National:Local, difference is not significant
    3 the National:Local difference and the Regional:Local, difference are both significant
    There is a "2-way condition table" in the updated spreadsheet that shows how these conditions are given values. Essentially, I picked one and multiplied it's true/false value (0 or 1) by 2 and added that to the the other condition's true/false value (also 0 or 1) so that the aggregate returns 0, 1, 2, or 3. The conditional format tests for the last three conditions.

    Note too that the conditional format uses cell addresses rather than named ranges. I'm not sure why, but it didn't seem to work with named ranges.

    I've set the conditional formatting up to work on data organised horizontally, as per your example, and for the original vertical format that the significance tests were laid out in.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sig Diff for proportions formula (Excel xp)

    well, now they have changed it and only want me to highlight the cell if there is sig diff between the natl and individual store's data. I've attached how i'm going to do it, could you please look at the red cells and tell me if the formula is correct? thank you very much.

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Sig Diff for proportions formula (Excel xp)

    Hi jha,

    I see you're still using the formula that requires numbers of observations, but inputting percentages. Here's a corrected version - see bolded cells.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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