in this tutorial i'm going to show you how to perform a chi-squared test of independence by using microsoft excel this includes step-by-step instructions to generate a p-value as always please do drop a like on the video if you find it useful and without further ado let's jump into excel and get started for this example let's say i have a sample of 200 people that visited my local pub 100 of these were males and 100 were female from these subjects i asked them if they were a smoker or non-smoker so there were 29 males that smoked and 71 that didn't and for the females there were 16 smokers and 84 non-smokers since these are the actual values for my experiment they are known as the observed values what i want to do is to perform a chi-squared test of independence to see if there is an association between gender and smoking status in my sample the first step to performing a chi-squared test is to add up each of the rows and columns in the contingency table here by using the sum function so let's add up the values for the smokers column to do this in a new cell i will enter equals sum open bracket then i'll highlight the cells in the column close the bracket and then press the enter key so in total there were 45 smokers i now need to repeat this process for the next column to copy the formula across i can simply select the cell i just calculated and you see the little green square in the lower right corner of the cell well click and drag this across to copy the formula excel will then calculate the sum for the non-smokers column in this example as you can see there were 155 non-smokers in total in my sample next i need to do a similar process but calculate the sum of the rows so to do this for the males i will select an empty cell then i will enter equals sum open bracket then i will highlight the two cells in the first row close the bracket and then press the enter key and as mentioned at the start you can see that there are 100 males in this sample again i will select this cell and copy the formula down to the cell underneath by dragging the little green square the final thing we need to do here is to calculate the total to do this you can either calculate the total value of the columns or the rows since the answer will be the same either way i will calculate this using the rows values so in a new cell i will again enter equals sum open bracket then i'll highlight the row sums then i will close the bracket and then press the enter key as expected we have a total value of 200 moving on we next need to work out the expected value for each entry in the table to work out the expected value you must multiply each row total by each column total and then divide that answer by the overall total let's do this for the male smokers so here i'll enter equals open bracket then i'll select the cell containing the row total then i'll multiply this by the cell containing the columns hotel i'll then close the bracket and divide by the cell containing the overall total and finally i'll press the enter key to run the formula so we would expect that there would be 22.5 male smokers in this example i'll repeat this process for the male non-smokers so in a new cell i will enter equals open bracket then i'll select the cell containing the row total then i'll multiply this by the cell containing the column total i'll then close the bracket and divide by the cell containing the overall total then i'll press the enter key so we would expect to see 77.5 male non-smokers in this example i now need to repeat this process for the females i'll speed this up since the process is exactly the same as i've just shown you for the males now i have all my expected values the next step is to subtract the expected from the observed values square it and then divide by the expected value so i'll do this for the male smokers first in a new cell i'll enter equals open bracket then i will select the observed value which is this cell here subtract the cell containing the expected value and then i'll close the bracket to square this you enter the carrot symbol then two then i'll divide this answer by the cell containing the expected value finally i'll press enter to work this out i get an answer of 1.88 when rounded i now need to repeat this process for the rest of my table again i can use excel's auto complete feature by selecting the small green square in the corner of the cell and dragging it down then i will drag it across to the right now i have all the values for my table next we need to calculate the chi-squared value and to do this we simply add up all of the values in this table we just calculated so in a new cell i will enter equal sum open bracket then i'll select all of the cells in the table i'll then close the bracket and then press enter to perform the calculation so the chi-squared value for this experiment is 4.85 when rounded next we need to calculate the degrees of freedom here the degrees of freedom is calculated by firstly subtracting 1 from the number of rows in the test then multiply this answer by the number of columns in the test subtract one so my example this would be two subtract one multiply by two subtract one which gives an answer of one so i will add one to this cell here the final step in performing the chi-squared test is to take the chi-square and degrees of freedom values and work out the p-value to do this in a new cell i will enter h equals s q dot dist dot r t open bracket then i will select the cell containing the chi square value add a comma then i will select the cell containing the degrees of freedom i'll close the bracket and then press the enter key to run the formula i get a p-value of 0.028 when rounded to interpret the p-value i will state my two hypotheses my null hypothesis was there is no association between gender and smoking status and my alternative hypothesis was there is an association between gender and smoking status if my alpha level or significance threshold was set at 0.05 this would mean i would fail to reject the null hypothesis if p is greater than 0.05 on the other hand if p is less than 0.05 i will reject the null hypothesis and accept the alternative hypothesis in this case my p-value was 0.028 since this value is less than 0.05 i will reject the null hypothesis and accept the alternative hypothesis therefore there does seem to be an association between gender and smoking status going back to excel there is in fact a function that you can use to calculate the chi-square p-value by just using the observed and expected table values to do this in a new cell enter equals chi sq dot test open bracket then highlight the values in the observed table add a comma then highlight the values in the expected table close the bracket and then press the enter key and as you can see the p-value is the same as we calculated previously and that wraps up this video tutorial you now know how to perform a chi-square test of independence by using microsoft excel if you found this video useful please leave a like it really does help support the channel if you've got a question pop it down in the comments below also consider subscribing for more weekly tutorials