How to Calculate Significant Difference Between Two Means in Excel

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.

Dataset for how to calculate the significant difference between two means in excel

method

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.

using the T.TEST function to calculate the significant difference between two means in excel

  • We will get the result in cell C19.
method

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.

Add-ins to calculate the significant difference between two means in excel

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

Using the DATA ANALYSIS to calculate the significant difference between two means in excel

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

T-test to calculate the significant difference between two means in excel

  • Finally, we will get a table with our results.

two tail result for calculating the significant difference between two means in excel


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.

Using the Fill Handle for Statistical Significance Calculator

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

Using the if function for Statistical Significance Calculator

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

Using the if function for Statistical Significance Calculator

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

Z score for Statistical Significance Calculator

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

Using the normdist function for Statistical Significance Calculator

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

Using the if function for a 90% confidence level

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

Using the if function for a 95% confidence level

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

Using the if function for a 99% 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.

(Visited 39 times, 1 visits today)
Md. Ahaduzzaman

Md. Ahaduzzaman

Hello everyone i'm Md.Ahaduzzaman.I am a civil engineering graduate from BUET.I am enthusiastic and highly motivated to always learn something new to broaden my horizon and find my lacking. Hopefully my Articles will benefit you to understand and overcome your problem.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo