P-value is used in Co-relation and regression analysis in excel which helps us to identify whether the result obtained is feasible or not and which data set from result to work with the value of P-value ranges from 0 to 1, there is no inbuilt method in excel to find out P-value of a given data set instead we use other functions such as Chi function.
P-Value is nothing but the probability value expressed in percentage value in hypothesis testing to support or reject the null hypothesis. P-Value or Probability Value is a popular concept in the statistical world. All the aspiring analysts should know about the P-Value and its purpose in data science. A frequency of the data points is called the hypothetical frequency and observed significance level for the test hypothesis.
- P-value is denoted by decimal points, but it is always a good thing to tell the result of the P-value in percentage instead of decimal points. Telling 5% is always better than telling the decimal points 0.05.
- In the test conducted to find the P-Value, if the P-value is smaller then, the stronger evidence against the null hypothesis and your data is more important or significant. If the P-value is higher, then there is weak evidence against the null hypothesis. So, by running a hypothesis test and finding the P-value, we can actually understand the significance of the finding.
How to Calculate the P-Value in T-Test in Excel?
Below are the examples to Calculate P Value in Excel T-Test.
P-Value Excel T-Test Example #1
In excel, we can find the P-Value easily. By running T-Test in excel, we can actually arrive at the statement whether the null hypothesis is TRUE or FALSE. Look at the below example to understand the concept practically.
Assume you are supplied with the weight loss process through diet data, and below is the data available to you to test the null hypothesis.
Step 1: First thing we need to do is calculate the difference between before diet and after diet.
The output is given below:
Drag the Formula to the rest of the cells.
Step 2: Now go to the Data tab, and under the data, tab click on Data Analysis.
Step 3: Now scroll down and find T.Test: Paired Two Sample for Means.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Step 4: Now select Variable 1 Range as before diet column.
Step 5: Variable 2 rang as after a diet column.
Step 6: Alpha value will be default 0.05, i.e., 5%. To retain the same value.
Note: 0.05 and 0.01 are often used common levels of significance.
Step 7: Now select the Output Range, i.e., where you want to display your analysis results.
Step 8: Click on the OK. We have analysis results from cell F1.
Ok, we have results here. P-value with a one-tail test is 0.078043, and P-value with the two tail tests is 0.156086. In both cases, P-value is greater than the alpha value, i.e., 0.05.
In this case, the P-value is greater than the alpha value, so the null hypothesis is TRUE, i.e., weak evidence against the null hypothesis. This means they’re actually very close data points between two data points.
P-Value Excel Example #2 – Find P-Value with T.TEST Function
In excel, we have a built-in function called T.TEST, which can give us the P-Value result instantly.
Open the T.TEST function in any of the cells in the spreadsheet.
Select the array 1 as before the diet column.
The second argument will be after the diet column, i.e., array 2
Tails will be one-tailed distribution.
The type will be Paired.
Now close the formula, we will have a result of P-Value.
So, we have P-Value, i.e., 0.078043, which exactly the same as the previous test of the analysis result.
Things to Remember
- You can change the significance level (alpha value) at different levels and arrive at the P Values in excel at different points.
- The common alpha values are 0.05 and 0.01.
- If the P-value is >0.10, then data is not significant; if the P-value is <=0.10, then the data is marginally significant.
- If P-Value is <=0.05, then the data is Significant, and if the P-value is <0.05, then the data is highly significant.
This has been a guide to P-Value in Excel. Here we discuss how to Calculate P-Value in Excel T-Test along with practical examples and downloadable excel template. You may learn more about excel from the following articles –