Question

Can anyone help me on how to do this in excel/what formulas to use?

The office occupancy rates were reported for four California metropolitan areas. Do the following data suggest that the office vacancies were independent of the metropolitan area? Run a hypothesis test at alpha of 0.05. What is your conclusion? Observed Frequencies Occupancy Status/Metropolitan Area Occupied Vacant Total Los Angeles San Diego San Francisco San Jose Total 192 AC) 200 116 34 150 133 775 26 225 200

Answer 1

Step 1. We put the value in a table and add them up to give the row total and col total and the grand total. As shown below.
Step 2 . We divide each value in the table with the grand total to get the frequency percentage.

Observed frequency Occupancy status Occupied Vacant col total Los Angeles San Diego San Francisco San Jose row Total 160 40 200 116 34 150 192 174 26 200 642 133 775 225 Expected value formulas Los Angeles (200*642)/775 (200*133)/775 Occupancy status San Francisco San Diego (150 642)/775 (150 133)/775 San Jose Occupled (225*642)/775 (200 642)/775 Vacant (225 133)/775 (200 133)/775 Expected value Occupancy status Occupied Vacant Los Angeles San Diego San Francisco San Jose 165.68 34.32 124.26 25.74 186.39 38.61 165.68 34.32

Step 3. We need to find the expected distribution which is done below.
Step 4. We find the chi stat using the formula given below.

16 Occupied 17 Vacant 165.68 34.32 124.26 25.74 186.39 38.61 165.68 34.32 19 chi stat Calculation 20 Los Angeles San Diego San Francisco San Jose 21 Occupied (160-165.68)A2/165.68 (116-124.26) 2/124.26 (192-186.39)A2/186.39 (174-165.68)A2/165.68 40-34.32)2/34.32 (34-25.74)2/25.74 33-38.61)2/38.61 26-34.32)A2/34.32 22 Vacant 23 24 chi stat Calculation (O- E) 25 26 Occupied 27 Vacant 28 29 30 df Los Angeles San Diego San Francisco San Jose 0.19 0.94 0.55 2.65 0.17 0.82 0.42 2.02 Pvalue CHISQ DIST(C29,3,TRUE) 0.0514 0.0514070149527428 1 Chi square test- pvalue CHISQ.TEST(B3:E4,B16:E17) 32 0.05141

Using chi table we find pvalue for the df = 3,
pvalue = 0.05141

Hypothesis
H0 : Office vacancies is independent of the metropolitan area
H1 : Office vacancies is not independent of the metropolitan area

Since the pvalue is greater than 0.05, we fail to reject the null hypothesis.
We conclude the that office vacancies is independent of the metropolitan area