Data Analysis Homework Excel Directions  Chi-Square Test Use the Chi-Square Test if you want to see if there is an association (relationship) between two or more nominal variables. In other words, see if there is a

Data Analysis Homework

Excel Directions

 Chi-Square Test

Use the Chi-Square Test if you want to see if there is an association (relationship) between two or more nominal variables. In other words, see if there is a difference between two means.

Chi Square: Example Question

Is there a relationship between gender and whether someone works in REC or not?

1.      Code data/survey results (in this example the coding has already been done for you, it can be found in cells A2:H34)

2.      Make a frequency distribution table of observed scores. Make sure it includes total columns and percentages.  (cells B8:C13). Percentages are calculated by taking the total number of those in each category and dividing it by the total number of those surveyed.

Calculate expected scores if there were no relationship for each variable. For example, if there were no difference between gender and whether or not someone was working in REC, then we would expect that about 50% of men would be working in REC. The 50% is the number you calculated in the previous step.

a.      Create a table of expected scores, it should look similar to the observed tables, but without the total and percentage column.

b.      To get the expected numbers, multiply the total number of men and the percentage of the population working in REC. For B12 the equation would be  =B10*E8

3.      Run the ChiTest Function.

a.      Click on the fx button on the top of the screen. Type in chitest in the search function.

b.      Select observed (actual) range of scores, do not include total or percent (B8:C9). Then select the expected range of scores (second table, B12:C13).

c.      Click OK

4.      Round your answer to the nearest hundredth. The hundredth is the second decimal point.
In this case p = .69. In the recreation we tend to use p<.05 as our significance level. That means if your p-value is bigger than .05 there is no significant difference between the two means (or groups) you just measured.

5.      So, is there a relationship between gender and whether someone works in REC or not?

No, there is no relationship between gender and whether someone works in REC or not.

Chi Square: Practice Question

1.      Try the practice question on your own

Is there a relationship between gender and which track people report they are in?

2.      The answer to this question is .89. Show your work to receive credit and answer the question on the answer sheet.

Correlation

Use the correlation to see if there is a relationship between two interval/ratio variables. You would use this if you had two interval/ratio intervals.

Correlation: Example Question

Is there a relationship between number of years enrolled and excitement for the major?

1.      Code data/survey results (in this example the coding has already been done for you, it can be found in cells M3:T29)

2.     

Sort the columns in order to select the "array" you want- so if you want to measure years be sure to sort the years column. To sort select the entire Data Book (M2:T34) and click the Sort button.  The select Sort by (for this example select years), click OK.

3.     

Run the Pearson Correlation Function

a.      Click on the fx button on the top of the screen. Type in correl in the search function.

b.      Select Array 1 range. This range should be the number of years enrolled (R3:R34).

c.      Select Array 2 range. This range should be excitement for the major (Q3:Q34).

d.      Click OK.

4.      Round your answer to the nearest hundredth. The hundredth is the second decimal point.
In this case r = .14. Remember that the Pearson Correlation function is not based on p<.05. It is based on the strength of the relationship from 0 to 1 or -1 to 0. The closer the number to 1, the stronger the relationship between the two variables.

5.      So, is there a relationship between number of years enrolled and excitement?
No, there is no relationship between gender and number of field hours worked.

Correlation: Practice Questions

1.      Try the practice questions on your own.

2.      The answer to the first practice question is .07. The answer to the second practice question is .20. Show your work to receive credit and answer the question on the answer sheet.

T-Test

Use the t-test if you want to see if there is a difference between two means. You would use this if you have a nominal variable and then an interval/ratio variable. In other words, is there a difference between males and females and which track they select or is there a difference between whether someone works in REC and their excitement with the major.

T-test: Example Question

Is there a difference between gender and number of field hours worked?

1.      Code data/survey results (in this example the coding has already been done for you, it can be found in cells L3:S34)

2.      Sort by the nominal variable, in this case gender.

a.      Select (highlight) all of the cells in the data workbook (L3:S35)

b.      Click on Data on the toolbar at the top

c.      Click on Sort (middle of the screen)

d.      Sort By – Are you a Male or a Female?

e.      Sort On – Values

f.       Order – Smallest to Largest

g.      Click OK

3.      Run the T-test Function

a.      Click on the fx button on the top of the screen. Type in ttest in the search function.

b.      Select Array 1 range. This range is just for the females and the number of field hours they have worked (select N3:N25).

c.      Select Array 2 range. This range is just for the males and the number of field hours they have worked (selected N26:N34).

d.      Select 2 tails.

e.      Select type 3 because we are looking at independent samples.

f.       Click OK.

4.      Round your answer to the nearest hundredth. The hundredth is the second decimal point.
In this case p = .82. In the recreation we tend to use p<.05 as our significance level. That means if your p-value is bigger than .05 there is no significant difference between the two means (or groups) you just measured.

5.      So, is there a difference between gender and number of field hours worked?

No, there is no relationship between gender and number of field hours worked.

6.      Make sure you write down your answer independent of the formula, once you resort the columns above your answer will change because the values in your selected areas will change.

T-test: Practice Question

1.      Try the practice questions on your own.

2.      The answer to the first practice question is .64. The answer to the second practice question is .03. Show your work to receive credit and answer the question on the answer sheet.