Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Picture Proportion During Printing | Excel Discussion (Misc queries) | |||
Display sample population size in chart i.e. n=X | Charts and Charting in Excel | |||
Pull a Sample from a Population | Excel Worksheet Functions | |||
Make a sheet to enter score and score is copy to the proper cell.. | Excel Worksheet Functions | |||
Raise numbers in the same proportion.. | Excel Discussion (Misc queries) |