Chi-Square Test in Excel Using VBA

​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.

chi1.PNG

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.)

chi2.PNG

When you run the code you will be prompted to select the data range using the cursor. See the example below:

chi3.PNG
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:

chi4.PNG

The next part of the code calculates the chi-square statistic, as shown below:

chi5.PNG
chi6.PNG
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.

chi7.PNG
The next prompt you see shows you the chi-square statistic, as shown below:

chi8.PNG
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.

chi9.PNG

Then you will see the statistics printed below the table.

chi10.PNG
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.

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 1
 
Michael posted on 9/21/2017 3:04 PM
Nice solution to a problem.

I teach elementary stat using EXCEL along with my day job in IR :) I too bemoan the lack of a chi-square analysis in EXCEL. Since many of my students definitively have no idea of doing VBA, I teach them how to do a matrix array function to calculate the expected. and then use the two built-in functions: p-value for χ2=0.00048775 =CHISQ.TEST(B3:C4,G3:H4) where you enter the observed and expected data ranges, and 12.1619258=CHISQ.INV.RT(B8,1) [B8 had the value from the p-value calculation] where the supplied data is probability and degrees of freedom. Had a picture where I show the matrix steps, but can't add pictures here.