Afia Kona
If you want to calculate P-Value from Confidence Interval in Excel, you have come to the right place. Here, we will walk you through 3 easy and effective methods to do the task smoothly.
Download Practice Workbook
You can download the Excel file and practice while you are reading this article.
What Is Confidence Interval?
A Confidence Interval (Cl) is an area around a calculation that displays how accurate the calculation is. The 95% Cl is the most commonly used Cl. However, there are some other confidence interval levels as well, they are 90% Cl or 99% Cl. The 95% Cl has Upper limits (u), and lower limits (l). The upper and lower limits of the 95% Cl predict the limit of the range where a true mean value might exist.
What Is P-Value and Null Hypothesis?
P-Value is also recognized as a Probability Value. It is used to acknowledge the statistical significance of an outcome.
A Null Hypothesis is a claim that there is no relation between two quantities, or between the observed result and the expected result.
P-Value is significant in statistics as it tests if the Null Hypothesis is significant or not.
Significance of P-Value:Â In statistics, the P-Value is always between 0-1.
- When the P-Value is less than 0.5 or the P value is less than 50 %, it indicates the Null Hypothesis is insignificant. Therefore, a lower P-Value indicates a strong relationship between 2 quantities, or between the observed result and the expected result.
- When P-Values equals 0.5, it indicates a marginally significant relationship between two quantities, or between the expected result and the observed result.
- When the P-Value is Greater than 0.5 or the P value is greater than 50 %, it indicates the Null Hypothesis is strong and significant. Therefore, a higher P-Value indicates no relationship between 2 quantities, or between the observed result and the expected result.
3 Methods to Calculate P-Value from Confidence Interval in Excel
In the following dataset, you can see Estimate of Effect, Est in cell C4, Upper limits of 95% Cl, u in cell C5, and Lower limits of 95% Cl, u in cell C6. Next, we will go through 3 easy methods to calculate P-Value from Confidence Interval. Here, we used Microsoft Office 365 to do the task. You can use any available Excel version.
1. Using EXP Function to Calculate P-Value from Confidence Interval
If you have a Confidence Interval for the difference between two quantities or two proportions, you can use the below formula, where we will use the EXP function to calculate P -value from Confidence Interval.
We know, that for a 95% Confidence Interval, the formula for calculating the P value is
P = exp(−0.717×z − 0.416×z2)Here,
- Test statistic Z = Est/SE
- The standard error SE = (u − l)/(2×1.96)
- Est = Estimate of Effect
- u = upper limits of a 95% CI
- l = lower limits of a 95% Cl
Next, let’s say we have a risk difference of 1.7, this is the estimate of effect EST between two qualities.
Along with that, we have upper limits u of a 95 percent confidence interval of 3.9.
Moreover, we have lower limits l of a 95 percent confidence interval of -0.57.
Further, we will calculate SE, Z, and P-value.
Steps:
- First, we will type the following formula in cell C7 to calculate Standard Error, SE.
=(C5-C6)/(2*1.96)Here, the formula simply subtracts C7 from C9 and divides the result by (2*1.96).
- After that, press ENTER.
As a result, you can see Standard Error, SE in cell C7.
- After that, we will type the following formula in cell C8 to calculate Test Statistics, Z.
=C4/C7Here, the formula simply divides cell C4 by C7.
- At this point, press ENTER.
Therefore, you can see Test Statistics, Z in cell C8.
- Afterward, we will type the following formula in cell C9 to calculate P-Value.
=EXP((-0.717*C8)-(0.416*C8^2))The EXP function returns the value of the constant e raised to the power of a specified number.
- Furthermore, press ENTER.
Therefore, you can see the P-Value in cell C9.
Here, you can easily notice that the P-Value is 0.13521626 which is less than 0.5. Therefore, it indicates the Null Hypothesis is insignificant. Therefore, this P-Value indicates a strong relationship between 2 quantities.
Read More: How to Calculate 95 Percent Confidence Interval in Excel (4 Ways)
2. Use of LN and EXP Functions to Calculate P-Value from Confidence Interval in Excel
If you have a Confidence Interval for the ratio between two quantities or two proportions, you can use the below formula to calculate the P -value.
We know, that for a 95% Confidence Interval, the formula for calculating the P-value is
P = exp(−0.717×z − 0.416×z2)Here,
- Est = Estimate of Effect
- u = upper limits of a 95% CI
- l = lower limits of a 95% Cl
- Test statistic Z = Est/SE
- The standard error SE = (u − l)/(2×1.96)
In the following dataset, you can see that we have an Estimate of Effect, ESt in cell C4.
Along with that, we have Lower limits of a 95% Cl,l in cell C6.
Moreover, we have Upper limits of a 95% Cl,u in cell C9.
 Here, for the risk ratio, we will take natural Log (Ln) for Est, therefore, we will take Ln(0.83) for Est value.
Along with that, we will take Ln(0.97) for u.
Furthermore, we will take Ln(0.6) for l.
Next, we will calculate SE, Z, and P-value by using the LN and EXP functions.
Steps:
- In the beginning, we will type the following formula to calculate the Log Transform of Est in cell C5.
=LN(C4)The LN function yields the natural Logarithm of a given value.
- After that, press ENTER.
Therefore, you can see the Log Transform of Est in cell C5.
- Next, we will type the following formula to calculate the Log Transform of l in cell C7.
=LN(C6)
- Moreover, press ENTER.
Then, you can see the Log Transform of l in cell C7.
- Afterward, we will type the following formula to calculate the Log Transform of u in cell C9.
=LN(C8)
- Then, press ENTER.
As a result, you can see the Log Transform of u in cell C9.
- Next, we will type the following formula in cell C10 to calculate Standard Error, SE.
=(C9-(C7))/(2*1.96)Here, the formula simply subtracts C7 from C9 and divides the result by (2*1.96).
- After that, press ENTER.
As a result, you can see Standard Error, SE in cell C10.
- Moreover, we will type the following formula in cell C11 to calculate Test Statistics, Z.
=C5/C10Here, the formula simply divides cell C5 by C10.
- At this point, press ENTER.
Therefore, you can see Test Statistics, Z in cell C8.
- Then, we will type the following formula in cell C12 to calculate P-Value.
=EXP((-0.717*(-C11))-(0.416*C11^2))Here, one thing must be remembered we take -C11 in the formula. This is because need a positive Z value in the formula.
The EXP function returns the value of the constant e raised to the power of a specified number.
- Next, press ENTER.
Therefore, you can see the P-Value in cell C12.
Here, you can easily notice that the P-Value is 0.128475323 which is less than 0.5. Therefore, it indicates the Null Hypothesis is insignificant. Therefore, this P-Value indicates a strong relationship between 2 quantities.
Read More: How to Find Upper and Lower Limits of Confidence Interval in Excel
3. Using NORMSDIST Function
In this method, we will use the NORMSDIST function to calculate P-Value from confidence interval.
Steps:
- In the beginning, we followed Method-1 to calculate Standard Error, SE, and Test Statistics, Z.
- After that, we type the following formula in cell C9.
=2*(1-NORMSDIST(C8)) 
Formula Breakdown
- NORMSDIST(C8) →The NORMSDIST function yields the standard normal cumulative distribution function.
- NORMSDIST(1.49082774) → becomes
- Output: 0.931996636684545
- 2*(1-0.931996636684545)→ becomes
- Output: 0.136006727
- Explanation: 136006727 is the P-Value.
- After that, press ENTER.
As a result, you can see the P-Value in cell C9.
Here, you can easily notice that the P-Value is 0.136006727 which is less than 0.5. Therefore, it indicates the Null Hypothesis is insignificant. Therefore, this P-Value indicates a strong relationship between 2 quantities.
Read More: How to Calculate 90 Percent Confidence Interval in Excel
Practice Section
You can download the Excel file above to practice the explained methods.
Conclusion
Here, we tried to show you 3 methods to calculate P-Value from Confidence Interval in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.
