By Lee Allard, Dean of Planning and Institutional Effectiveness at Hagerstown Community College
In this month’s Tech Tip, we’ll look at using VBA code for running a chi-square test in Excel. (Note: this tip does require some basic knowledge of VBA programming in Excel.)
The Data Analysis add-in to Excel offers a number of basic statistical analyses (e.g. regression, t-test, ANOVA, etc.), but curiously, a chi-square test is not among them. However, this is a very simple statistical procedure which can be easily coded in VBA. After the code is created, you’ll be able to run it as a macro named “ChiSquare”. (Note: there are some chi-square functions in Excel, but these functions do not allow you to directly perform a chi-square test.)
Let’s say we have a very simple contingency table like the following, which compares enrollment rates for male and female students.
From a statistical standpoint, we might be interested in the question of whether there is a significant difference by gender in enrollment rates. The simplest way to check for this is by using a chi-square test.
Below is the first part of the code as seen in the VBA editor, which prompts the user to enter the data range and then determines the number of rows and columns. (Note: If you copy and string together each of the sections of code presented below and paste them into your VBA editor in Excel, the program as a whole should work fine.)
When you run the code you will be prompted to select the data range using the cursor. See the example below:
Then use you mouse to select the data (not the row or column names) as shown below, or you can just type in the input the range in the prompt box:
The next part of the code calculates the chi-square statistic, as shown below:
This code basically duplicates the steps you would take if you were calculating the chi-square statistic by hand. The last line of code above calls the ChiDist function in Excel to determine the p-value for the chi-square statistic.
The last piece of code displays the test statistic, and gives you the option of printing it in the worksheet.
The next prompt you see shows you the chi-square statistic, as shown below:
You will then see a prompt asking if you want to print the statistics in the worksheet. If so, just type “Y” at the prompt.
Then you will see the statistics printed below the table.
For those readers who are a still a bit unsure about using VBA, it is possible to indirectly calculate a Chi Square and its p value in Excel if you know some basic statistics and are able to calculate the expected values for your observed values. For more information, you can refer to appropriate the Microsoft documentation or other online support resources.
One final note - this Tech Tip is obviously not designed to give a lesson in statistics! There are many possible statistical scenarios that could arise when working with contingency tables. The results here are designed to duplicate the results you would get running a basic chi-square analysis in SPSS (for example) with a Pearson test statistic.