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 in Excel (Table of Contents)
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 called as 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 P value we can actually understand the significance of 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 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 rest of 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.
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 Out Put Range i.e. where you want to display your analysis results.
Step 8: Click on OK. We have analysis results from cell F1.
Ok, we have results here. P value with one tail test is 0.078043 and P value with two tail test is 0.156086. In both the 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 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 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 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 P value is <=0.10 then the data is marginally significant.
- If P-Value is <=0.05 then the data is Significant and if 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 –