Md. Ahaduzzaman
Sometimes we need to calculate significant differences between the two means to understand which one is the better approach. For instance, in a football match, two different teams can follow different formations and approaches. In the agricultural sector, we can use two different fertilizers for plant growth. To understand which one performed better we need to calculate the significant difference between them. For this calculation is not only time-consuming but also difficult too. Using Excel we can easily do this calculation. That is why in this article we will discuss two easy ways how to calculate the significant difference between two means in excel.
📁 Download Excel File
Download practice file from here.
Learn to Calculate Significant Difference Between Two Means with These two Methods
Here in this article, we are going to learn two distinct methods to calculate significant difference between two means in Excel. Let’s assume we have the dataset of the accident occurrence for a particular road for a year. We have made some maintenance to improve the quality of the road and decrease the accident frequency. Now we are trying to understand by determining significant difference between the two means whether the maintenance work has improved the road quality or not. Our sample dataset for demonstration purposes is as follows.
1. Using T.TEST Function in Excel Formula
Here in this step, we will learn how to use the T.TEST function to calculate the significant difference between two means in Excel. So the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the formula box of cell C19 and write this formula and hit Enter.
=T.TEST(C6:C17,D6:D17,2,1)
🔨 Formula Breakdown
👉 Here C6:C17 is our first array for the sample.
👉 Here D6:D17 is our second array for the sample.
👉 2 means it is a two-tailed distribution.
👉 1 denotes our type is paired.
- We will get the result in cell C19.
2. Applying Data Analysis Tool for Two Sample Test in Excel
Here in this section, we will discuss how to apply the Data Analysis tool for a two-sample t-test in Excel. Therefore, the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we press these three buttons together.
Alt+F+T
- Following that, we go to the Add-ins.
- After that, we select the Analysis Toolpak.
- Finally, we go to the Go option.
- We select the Analysis Toolpak Add-ins in the dialogue box.
- We then press OK.
- Now we go to the Data option from the Ribbon.
- After that, we select the Data Analysis option.
- We now select t-Test: Paired Two Sample for Means option.
- After that, we hit OK.
- We will get a dialogue box.
- Firstly we choose our two separate variable ranges.
- Following that we select the Labels.
- After that, we select the Alpha value and decide our Output Range.
- Finally, we select OK.
- Finally, we will get a table with our results.
How to Create Statistical Significance Calculator in Excel
In this section, we will learn how to make a statistical significance calculator in Excel. In this demonstration, an attempt to increase the number of visitors to the Dachfenster Rollo website and convert customers by making a purchase is shown. So the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly our sample dataset is collected.
- After that, we calculate the conversion in cell F6 by this formula.
=D6/C6
- We use the Fill Handle tool to drag the formula in column F.
- Following that we type this formula in cell G6.
=SQRT((F6*(1-F6)/C6))
- We again use the Fill Handle to drag the formula in column G.
- We now type this formula in F11.
=IF(F6-1.65*G6<0,0,F6-1.65*G6)
🔨 Formula Breakdown
👉 Here F6-1.65*G6<0 is our logical test.
👉 0 is our result if the test result is true.
👉 Otherwise the result is given by F6-1.65*G6.
- We now type this formula in cell G11.
=IF(F6+1.65*G6>1,1,F6+1.65*G6)
🔨 Formula Breakdown
👉 Here F6+1.65*G6>1 is our logical test.
👉 1 is our result if the test result is true.
👉 Otherwise the result is given by F6+1.65*G6.
- We do the same process for 95% conversion rate limits.
- Finally, we again do the same thing for 99% conversion rate limits.
- Now we go to Cell C14 and type this formula in the formula box.
=(F6-F7)/SQRT(POWER(G6,2)+POWER(G7,2))
- We get the Z-score from this formula.
- Now we go to the C16 cell box and type this formula.
=NORMDIST(C14,0,1,TRUE)
🔨 Formula Breakdown
👉 Firstly here C14 is the variable.
👉 0 denotes mean.
👉 1 is the standard deviation.
👉 TRUE means cumulative.
- Finally, we get the probability of occurrence in cell C16.
- Now we go to cell D10 and type this formula.
=IF(OR($C$16<0.1,$C$16>0.9), “YES”, “NO”)
🔨 Formula Breakdown
👉 Here OR($C$16<0.1,$C$16>0.9) whichever is applicable is our logical test.
👉 YES is our result if the test result is true.
👉 Otherwise the result is NO.
- Now we go to cell D11 and type this formula.
=IF(OR($C$16<0.05,$C$16>0.95), “YES”, “NO”)
🔨 Formula Breakdown
👉 Here OR($C$16<0.05,$C$16>0.95) whichever is applicable is our logical test.
👉 YES is our result if the test result is true.
👉 Otherwise the result is NO.
- Again we go to cell D12 and type this formula.
=IF(OR($C$16<0.01,$C$16>0.99), “YES”, “NO”)
🔨 Formula Breakdown
👉 Firstly here OR($C$16<0.01,$C$16>0.99) whichever is applicable is our logical test.
👉 YES is our result if the test result is true.
👉 Otherwise the result is NO.
- Finally, we can say that the result is significant at a 95% confidence level.
📄 Important Notes
🖊️ Firstly, when we use the T.TEST function, we must carefully put the function argument.
🖊️ Also, we have to add the Add-ins first to bring Data Analysis tool to our Excel ribbon.
📝 Takeaways from This Article
📌 Firstly, we can use the T.TEST function to calculate significant difference between the two means in Excel.
📌 Apart from this, we can use the Data Analysis tool to calculate the significant difference between the two means in Excel.
Conclusion
In this article, we have learned two different ways to calculate the significant difference between the two means in Excel. We can use the T.TEST function in Excel to determine that. If we want we can also use the Data Analysis tool to do the same thing. Users can use any of the two methods. Also if you have any queries you can ask questions in our comment box. For more Excel-related problems and solutions you can visit our website www.ExcelDen.com.
