#CREATE SIMPLE MATH QUIZ WITH EXCEL HOW TO#

We can now calculate the p-value for the chi-square test statistic by using a formula of form =CHISQ.TEST( Obs, Exp, df) where Obs is the 3 × 3 array of observed values, Exp = the 3 × 3 array of expected values and df = (row count – 1) (column count – 1) = 2 ∙ 2 = 4. See Matrix Operations for more information about the MMULT array function. An alternative approach for filling in all the cells in the Expected Values table is to place the following array formula in range H6:J8 (and then press Ctrl-Shft-Enter): We then set every Expected Values cell toĮ.g.

We start by setting all the totals in the Expected Values table to be the same as the corresponding total in the Observed Values table (e.g. In this way, we can fill out the table for expected values. Thus, based on the null hypothesis, we expect that 10.0% of 175 = 17.5 people are from a wealthy family and have graduated from university. Similarly, the probability that someone in the sample graduated from university is 68/175 = 38.9%.īased on the null hypothesis, the event of being from a wealthy family is independent of graduating from university, and so the expected probability of both events is simply the product of the two events, or 25.7% ∙ 38.9% = 10.0%. Since 45 of the 175 people in the sample are from wealthy families, the probability that someone in the sample is from a wealthy family is 45/175 = 25.7%.

#CREATE SIMPLE MATH QUIZ WITH EXCEL HOW TO#

We now show how to construct the Expected Values shown in Figure 1. We also assume that the proportions from the sample are good estimates for the probabilities of the expected values. To accomplish this we use the fact (by Definition 3 of Basic Probability Concepts) that if A and B are independent events then P( A ∩ B) = P( A) ∙ P( B). We use the chi-square test, and so need to calculate the expected values that correspond to the observed values in the table above. H 0: The highest level of schooling attained is independent of parents’ wealth Based on the data collected can we conclude that a person’s level of schooling is independent of their parents’ wealth?įigure 1 – Observed data and expected values for Example 1 The results of the survey are summarized on the left side of Figure 1 (Observed Values). Example using CHISQ.TESTĮxample 1: A survey was conducted of 175 young adults, classifying their highest level of schooling as graduated from university, graduated from high school, or neither, and classifying their parents as wealthy, middle class, or poor. Instead, you need to use the equivalent function, CHITEST. The ranges R1 and R2 must have the same size and shape and can only contain numeric values.įor versions of Excel prior to Excel 2010, the CHISQ.TEST function doesn’t exist. For R1 = the array of observed data and R2 = the array of expected values, we haveĬHISQ.TEST(R1, R2) = CHISQ.DIST( x, df) where x is calculated from R1 and R2 as in Definition 2 of Goodness of Fit and df = (row count – 1) (column count – 1). Excel worksheet functionĮxcel Function: The CHISQ.TEST function described in Goodness of Fit can be extended to support ranges consisting of multiple rows and columns. In these cases df = (row count – 1) (column count – 1). Such data are organized in what are called contingency tables, as described in Example 1.

We can use the method described in Goodness of Fit to determine whether two sets of data are independent of each other.

Create simple math quiz with excel