Inferential Statistics with Excel--Remember that t-scores are most often used since population parameters required for use of z-scores are seldom known.

 

 

Excel 2007 & 2010

 

Confidence Intervals--Confidence intervals using z are performed with the CONFIDENCE FUNCTION in Excel 2007 (CONFIDENCE.NORM FUNCTION in 2010) within the function wizard (choose the Formulas Tab and select the Insert Function option or select the Function Wizard Tool from the formula bar). 

 

Confidence intervals using t are performed with the Confidence Level for the Mean item within the Descriptive Statistics option under the Data Analysis Tool (choose the Data Tab, select the Analysis Group and then select Data Analysis) in Excel 2007 and 2010.  You can also use CONFIDENCE.T in 2010 if you do not want the other descriptive statistics.

 

In both instances what is returned to you is z(or t) multiplied by the standard error (s or s divided by the sqrt of n).

                            z*(s/sqrt of n) or t*(s/sqrt of n)--Your text book refers to this as the margin of error

 

You can then take the sample mean minus and the sample mean plus this value to get the two points defining the confidence interval.  

 

Z-Test for One Mean--Use the ZTEST function in Excel 2007 (Z.TEST in Excel 2010) within the function wizard.  The function will guide you through the argument and return to you a p-value.  CAUTION:  It tells you it is returning a one-tailed p-value but this is not always the case.  It returns the one-tailed p-value for positive values of z.  It returns 1 minus the one-tailed p-value for negative values of z.  This requires some careful attention in order to properly draw your conclusions.

 

T-Test for One Mean--Excel does not provide a function for this.  The CD that came with your book may provide a macro for doing this test.  Another option is to simply do some intermediate calculations with Excel (standard deviations, means, etc.) and use these in the formulas either in Excel or on your calculator.  In this class, we will first calculate the sample mean and the standard error (s/sqrt of n).  Using the hypothesized m, we will write a formula in a cell in Excel that calculates t. 

 

Then in Excel 2007, the TDIST function can be used to get the one or two-tailed p-value for the single sample hypothesis test.  The TDIST function requires 3 arguments in the parentheses. 

 

               TDIST(x,df,tails)  Here x is the value of t calculated by the user via his or her own formula      

               (manually or in Excel), df=n-1, and tails is the type of test (1 or 2).

 

In Excel 2010, after calculating the t-score with your own formula, use the T.DIST.RT function for a one-tailed test.  Use the T.DIST.2T function for a two-tailed test.

 

2-Sample Matched Pairs T-Test-- In both Excel 2007 and 2010, go to the Data Tab.  In the Analysis Group, select Data Analysis and then pick t-test:  Paired Two Sample for Means.  Fill in the dialog box using cell references for your data locations, also highlight the labels for your data and check the labels box.  Your hypothesized mean difference will be 0.

  

2-Sample T-Test Unequal Variances--In both Excel 2007 and 2010, go to the Data Tab.  In the Analysis Group, select Data Analysis and then pick t-test:  Two Sample Assuming Unequal Variances.  Fill in the dialog box using cell references for your data locations, also highlight the labels for your data, and check the labels box.  Your hypothesized mean difference will be 0.

 

 

 

Excel 2003

 

Inferential Statistics with Excel--Remember that t-scores are most often used since population parameters required for use of z-scores are seldom known.

 

Confidence Intervals--Confidence intervals using z are performed with the CONFIDENCE FUNCTION within the function wizard.  Confidence intervals using t are performed with the Confidence Level for the Mean item within the Descriptive Statistics option under the Data Analysis Tool.  In the first case you do not need the array of data to use the function (you simply need the summary data requested in the function), however in the latter case you must have the full array of data in order to use the tool.  In both instances what is returned to you is z(or t) multiplied by the standard error (s or s divided by the sq.rt. of n). 

                          z(s/sq rt of n) or t(s/sq rt of n)--Your text book refers to this as the margin of error

 

You can then take the sample mean minus and the sample mean plus this value to get the two points defining the confidence interval.  

 

Z-Test for One Mean--Use the ZTEST function within the function wizard.  The function will guide you through the argument and return to you a p-value.  CAUTION:  It tells you it is returning a two-tailed p-value but it is not.  It returns the one-tailed p-value for positive values of z.  It returns 1 minus the one-tailed p-value for negative values of z.  This requires some careful attention in order to properly draw your conclusions.

 

T-Test for One Mean--Excel does not provide a function for this.  The CD that came with your book provides a macro for doing this test.  Another option is to simply do some intermediate calculations with Excel (standard deviations, means, etc.) and use these in the formulas either in Excel or on your calculator.  In this class, we will first calculate the sample mean and the standard error (s/sq rt of n).  Using the hypothesized m, we will write a formula in a cell in Excel that calculates t.  Then the TDIST function can be used to get the one or two-tailed p-value for the single sample hypothesis test.  The TDIST function requires 3 arguments in the parentheses. 

 

               TDIST(x,df,tails)  Here x is the value of t calculated by the user via his or her own formula      

               (manually or in Excel), df=n-1, and tails is the type of test (1 or 2).

 

2-Sample Matched Pairs T-Test--Select Tools/Data Analysis and then pick t-test:  Paired Two Sample for Means.  Fill in the dialog box as requested.

 

2-Sample T-Test Unequal Variances--Select Tools/Data Analysis and then pick t-test:  Two Sample Assuming Unequal Variances.  Fill in the dialog box as requested.