Open up the small Excel database that is linked below.
With this database, you will:
- Identify and list categorical and continuous variables.
- Calculate frequencies and percentages for all of the categorical variables, and means, and standard deviations for the continuous variables.
- Run a one-sample z test of proportions for one categorical variable. Here is an example in terms of wording: “Is the proportion of English speaking patients greater than 50%?” Using .05 level of significant test the appropriate hypotheses.
- Write the null and alternate hypotheses
- What is the proportion of English speaking patients
- What is the standard error and z statistics
- Find critical z and p value. Is the p value significant? Conclusion?
- Run an independent samples t-test to compare two groups of people (specified by a dichotomous variable) on one continuous variable. Using .05 level of significant test the appropriate hypotheses. In order to run this test statistics you have to re-arrange your data.
- Write the null and alternate hypotheses.
- What is the calculated mean for each group?
- What is the confidence interval for each group?
- What is the critical t value?
- Is the p value significant? Conclusion?
Need Excel help? Click on the Excel Function Help Workbook under Course Materials.
Expert Solution Preview
Introduction: In this assignment, you will be working with a small Excel database to identify and list categorical and continuous variables, calculate frequencies and percentages for categorical variables, means and standard deviations for continuous variables, run a one-sample z test of proportions for a categorical variable and an independent samples t-test to compare two groups of people on one continuous variable.
1. To identify and list categorical and continuous variables, open the Excel database and go through each column to determine the type of data it contains. Categorical variables are those that represent non-numerical data, such as gender, race, or occupation. Continuous variables are those that represent numerical data, such as age, height, or weight.
2. To calculate frequencies and percentages for categorical variables, use Excel functions such as COUNTIF and COUNTA to count the number of occurrences of each category and the total number of values in the column. Then, divide the count of each category by the total count to get the percentage. To calculate means and standard deviations for continuous variables, use Excel functions such as AVERAGE and STDEV.
3. To run a one-sample z test of proportions for a categorical variable, follow these steps:
– Write the null and alternate hypotheses. The null hypothesis would be that the proportion of English speaking patients is equal to 50%, while the alternate hypothesis would be that the proportion is greater than 50%.
– Find the proportion of English speaking patients by counting the number of patients who speak English and dividing it by the total number of patients.
– Calculate the standard error by dividing the square root of (p * (1 – p) / n) where p is the proportion from step 2 and n is the sample size.
– Calculate the z statistic by subtracting the hypothesized proportion from the sample proportion and dividing the result by the standard error.
– Find the critical z and p values using a z table or Excel function such as NORMSINV. If the calculated p value is less than 0.05, then the result is significant and we can reject the null hypothesis. Otherwise, we fail to reject the null hypothesis.
4. To run an independent samples t-test to compare two groups of people on one continuous variable, follow these steps:
– Write the null and alternate hypotheses. The null hypothesis would be that there is no difference between the means of the two groups, while the alternate hypothesis would be that there is a significant difference.
– Calculate the mean for each group using Excel functions such as AVERAGE and filter the data to separate the two groups.
– Calculate the confidence interval for each group using Excel functions such as CONFIDENCE.
– Find the critical t value using a t table or Excel function such as TINV.
– Calculate the t-statistic by subtracting the mean of one group from the mean of the other and dividing by the standard error.
– Find the p value using Excel function such as TDIST. If the p value is less than 0.05, then we can reject the null hypothesis and conclude that there is a significant difference between the two groups. Otherwise, we fail to reject the null hypothesis.