Results 1 to 13 of 13

20030501, 02:56 #1
 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.

20030501, 03:25 #2
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,092
 Thanks
 2
 Thanked 428 Times in 353 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 CI were set up for scenario modelling
Cheers
PS: System gremlins got to my last attempt at posting this.Cheers,
Paul Edstein
[MS MVP  Word]

20030501, 04:26 #3
 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?

20030501, 07:09 #4
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,092
 Thanks
 2
 Thanked 428 Times in 353 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+
CheersCheers,
Paul Edstein
[MS MVP  Word]

20030501, 17:59 #5
 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 cellis that correct? thanks

20030501, 18:32 #6
 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

20030501, 22:05 #7
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,092
 Thanks
 2
 Thanked 428 Times in 353 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.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20030505, 04:12 #8
 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.

20030505, 10:16 #9
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,092
 Thanks
 2
 Thanked 428 Times in 353 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.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20030505, 14:34 #10
 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>

20030506, 02:42 #11
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,092
 Thanks
 2
 Thanked 428 Times in 353 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 "2way 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.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20030508, 06:09 #12
 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.

20030508, 07:52 #13
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,092
 Thanks
 2
 Thanked 428 Times in 353 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.
CheersCheers,
Paul Edstein
[MS MVP  Word]