Chi Square Test with Excel or Google Sheet:, Expected Values, p-value, and Chi Square

Learn how to run a chi square test of independence with Excel or Google Sheets--the steps are the same. We will compute the expected values in Excel and Google sheets. From there, we'll use Excel and Google Sheets to compute Chi Square and find the p-value for the chi square test. I'll also show you a quicker way to compute the p-value for a chi square test that bypasses computing chi square.
▶ Watch
Chi-Square Using TI84 Calculator • Chi Square Goodness-of...
Chi-Square Goodness of Fit with Excel or Google Sheets • Chi Square Test Goodne...
⏱ Time Stamps
0:00 Observed Contingency Table
1:07 Expected Values Excel, Google sheets
2:52 Chi Square Excel, Google Sheets
4:20 Degrees of Freedom Excel, Google Sheets
4:40 P-value for Chi Square Excel, Google Sheets
5:10 P-value without using CHITEST Excel, Google Sheets

[@AngieTeaches]

I also have a Chi Test Goodness of Fit test video here kzbin.info/www/bejne/eIS4qaighK-Kpc0. Thanks for watching!!

[@user-yn8st2bf6r]

Thank you! Very Clear and Helpful

[@AngieTeaches]

You are so welcome. You made my day!

[@mikelopez2686]

LIfe saver! thank you!

[@AngieTeaches]

You are so welcome!!

[@JohnWhite-ms5wr]

I really hate reading textbooks so much, thank you for explaining this and how to get all the values. I kept looking at the formula and thinking, "I have no idea what my expected value is, or how I am supposed to calculate it."

[@AngieTeaches]

I feel the same way!! So glad this was helpful.

[@facundopucill7957]

Thanks

[@AngieTeaches]

So glad this was helpful, Facundo!

[@stavrosbatsaktsis8698]

Thank you very much for this extremely useful video, Angie! If I may ask, can we make the same process work for more than 2 preferred coffee places (e.g. 4-5 )? If yes, what steps should we take instead?

[@AngieTeaches]

Yes, absolutely. You will follow the same steps just with a larger table. To find the degrees of freedom, use (#columns-1)x(#rows-1). It's a really flexible test for whatever size your table is. Good question!

[@catwalkerauthor9536]

Hi there, this was such a great video, thanks. My issue is that my P values come up different using the two methods you show here! I have double checked all of the formulae and it still happens. I'm using a 4 row by 3 column table and can't figure out what I'm doing wrong. Any suggestions? Many thanks. Cat

[@catwalkerauthor9536]

Figured it out! I had some null values in there that I needed to replace with zeros for the CHISQ.TEST to work properly! :)

[@AngieTeaches]

I’m so glad you figured this out!! And thanks for the kudos 😊

[@oliviagramza8006]

Hi! I was wondering why it was better to use 1 degree of freedom rather than 3. The way I was taught, df = n-1, n being the number of "cases" you could have. What makes the df 1 instead of 3? Thanks so much for the helpful video!

[@AngieTeaches]

Great question!! There are two variables, and n-1 only works for a single variable. If we knew the total for each row and each column, how many cells would also have to be known for us to know the rest of the values? In this case, just one cell, so dF=1. Or, we could take one row away and one column away and be able to recreate the rest of the numbers.

[@oliviagramza8006]

@@AngieTeaches awesome! that makes sense, thanks so much!!

[@user-td5bz2tf5o]

@@oliviagramza8006 Just adding a bit to what Angie said: n-1 for _each_ dimension. Why the "-1"? Because one dof has been used up to specify the null hypothesis or the mean behavior. In the case of 1D data with 3 data points (a,b,c), the p-value (probability of getting a result (x,y,z) similar to or more extreme than (a,b,c)) is calculated by varying (x,y,z) assuming x+y+z = constant.

[@plunoko]

Is there a way to count it faster with bigger amount of data?

[@AngieTeaches]

Good question--Yes! You can use the "=count(" or "=countif(" functions. Go to th 3:21 mark in the video using this link where I show you how kzbin.info/www/bejne/rIOVZIOKnLiqrrc

[@robertolinger683]

Hi, Angie. I'm trying to get an answer to this question: Why did you use CHISQ.DIST.RT instead of CHISQ.DIST ?

[@AngieTeaches]

Great question! CHISQ.DIST gives the probability that the chi square value is less than or equal (to the left) and CHISQ.DIST is the probability the value is more than (the right tail). The 2 values sum to 1.