sample vs. population proportion using a z-score in excel
 

To all,

Needed some help on finding a formula to use in excel to calculate a z-
score that is comparing a sample proportion to a population
proportion. Here's the data:

Sample proportion = 0.31
N = 998
Population proportion = 0.49

I can find a z-score using a formula by hand: (z-score = -11.39), but
wanted to calculate in excel for many other variables. This is the
formula I used by hand:

p = sample proportion
Po = population proportion
alpha = 0.05 (z-critical = plus/minus 1.96)
non-directional test

z = (p-Po)/square root((Po(1-Po))/N)

When I use the standardize function in excel it gives me = -0.1153.

Any suggestions?
Thanks!

sample vs. population proportion using a z-score in excel
 

Hi,

First I would look at Excel's z-test option. To run this you need the
Analysis ToolPak attached - choose Tools, Add-ins, and check Analysis
ToolPak. Then choose Tools, Data Analysis, and scroll the list to the last
option z-test Two Sample for Means and click OK. See it this does what you
need.

Second, look at the ZTEST function and see if this does what you need.


--
Thanks,
Shane Devenshire


"Christopher" wrote:

To all,

Needed some help on finding a formula to use in excel to calculate a z-
score that is comparing a sample proportion to a population
proportion. Here's the data:

Sample proportion = 0.31
N = 998
Population proportion = 0.49

I can find a z-score using a formula by hand: (z-score = -11.39), but
wanted to calculate in excel for many other variables. This is the
formula I used by hand:

p = sample proportion
Po = population proportion
alpha = 0.05 (z-critical = plus/minus 1.96)
non-directional test

z = (p-Po)/square root((Po(1-Po))/N)

When I use the standardize function in excel it gives me = -0.1153.

Any suggestions?
Thanks!

sample vs. population proportion using a z-score in excel
 

Christopher -

=STANDARDIZE(0.31,0.49,SQRT(0.5*0.5/998))

returns -11.37280968

Your parentheses are misplaced in
z = (p-Po)/square root((Po(1-Po))/N)
The expression should be
z = (p-Po)/square root(Po(1-Po)/N)

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel



"Christopher" wrote in message
...
To all,

Needed some help on finding a formula to use in excel to calculate a z-
score that is comparing a sample proportion to a population
proportion. Here's the data:

Sample proportion = 0.31
N = 998
Population proportion = 0.49

I can find a z-score using a formula by hand: (z-score = -11.39), but
wanted to calculate in excel for many other variables. This is the
formula I used by hand:

p = sample proportion
Po = population proportion
alpha = 0.05 (z-critical = plus/minus 1.96)
non-directional test

z = (p-Po)/square root((Po(1-Po))/N)

When I use the standardize function in excel it gives me = -0.1153.

Any suggestions?
Thanks!

sample vs. population proportion using a z-score in excel
 

On Oct 27, 2:32*pm, "Mike Middleton" wrote:
Christopher *-

=STANDARDIZE(0.31,0.49,SQRT(0.5*0.5/998))

returns -11.37280968

Your parentheses are misplaced in
z = (p-Po)/square root((Po(1-Po))/N)
The expression should be
z = (p-Po)/square root(Po(1-Po)/N)

- *Mike Middletonhttp://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

"Christopher" wrote in message

...

To all,

Needed some help on finding a formula to use in excel to calculate a z-
score that is comparing a sample proportion to a population
proportion. Here's the data:

Sample proportion = 0.31
N = 998
Population proportion = 0.49

I can find a z-score using a formula by hand: (z-score = -11.39), but
wanted to calculate in excel for many other variables. This is the
formula I used by hand:

p = sample proportion
Po = population proportion
alpha = 0.05 (z-critical = plus/minus 1.96)
non-directional test

z = (p-Po)/square root((Po(1-Po))/N)

When I use the standardize function in excel it gives me = -0.1153.

Any suggestions?
Thanks!

Thanks for all your help! I really appreciate it!

Just another question; maybe you can help me out with this one.
If my p and Po values (.31 and .49 respectively) are derived from
another spreadsheet, how can I factor that into the formula? So for
example using the formula you gave me above,

=STANDARDIZE(C16,C18,SQRT((C18(1-C18))/C15))

cells C16 and C18 have the formulas =(C14/$B$14) and =(C17/$B$17)
respectively and N has the formula =COUNT(JCP!C:C)

Thanks for all your help Shane and Mike

sample vs. population proportion using a z-score in excel
 

On Oct 27, 2:32*pm, "Mike Middleton" wrote:
Christopher *-

=STANDARDIZE(0.31,0.49,SQRT(0.5*0.5/998))

returns -11.37280968

Your parentheses are misplaced in
z = (p-Po)/square root((Po(1-Po))/N)
The expression should be
z = (p-Po)/square root(Po(1-Po)/N)

- *Mike Middletonhttp://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

"Christopher" wrote in message

...

To all,

Needed some help on finding a formula to use in excel to calculate a z-
score that is comparing a sample proportion to a population
proportion. Here's the data:

Sample proportion = 0.31
N = 998
Population proportion = 0.49

I can find a z-score using a formula by hand: (z-score = -11.39), but
wanted to calculate in excel for many other variables. This is the
formula I used by hand:

p = sample proportion
Po = population proportion
alpha = 0.05 (z-critical = plus/minus 1.96)
non-directional test

z = (p-Po)/square root((Po(1-Po))/N)

When I use the standardize function in excel it gives me = -0.1153.

Any suggestions?
Thanks!

Thanks for all your help! I really appreciate.

I have another question. Maybe you can help me out on this on.

My p Po and N all have formulas from another worksheet within the same
file - how can I include this into standardize formula. For example,
using the formula you provided above I put together this formula:

=STANDARDIZE(C16,C18,SQRT((C18(1-C18))/C15))

C16 has the formula =(C14/$B$14) this is the value for p
C18 has the formula =(C17/$B$17) this is the value for Po
C15 has the formula =COUNT(JCP!C:C) this is the value for N

Any thoughts/suggestions? Thanks for all your help again Shane and Mike