What is the Pvalue for?
The Pvalue helps us determine the likelihood of the results from hypothetical tests. We analyze results based on two hypotheses: the Null hypothesis and the Alternative hypothesis. By using the Pvalue, we can assess whether the data supports the Null hypothesis or the Alternative hypothesis.
Dataset Overview
We have some predicted sales values and actual sales values of some of the products of a company.
Method 1 – Using tTest Analysis Tool
Here, we will use the Data Analysis Toolpak containing the tTest analysis tool to determine the Pvalue for the two sets of sales data.
 Activate the Data Analysis ToolPak (if not already enabled):
 Click on the File tab.

 Select Options.

 In the Excel Options dialog box, choose the Addins option on the left panel.
 In the Manage box, select Excel Addins and click Go.

 Check the Analysis ToolPak option and press OK.
 Access the Data Analysis Tool:
 Go to the Data tab.
 In the Analysis group, click on Data Analysis.
 Select the tTest: Paired Two Sample for Means:
 The Data Analysis wizard will appear.
 Choose the option tTest: Paired Two Sample for Means from the list of analysis tools.
 Provide Input Ranges:
 For Variable 1 Range, use the range $C$4:$C$11 (predicted sales values).
 For Variable 2 Range, use the range $D$4:$D$11 (actual sales values).
 Select an Output Range (e.g., $E$4).
 Set the Significance Level (Alpha):
 You can change the value for Alpha (significance level) from the default 0.05 to 0.01 if needed.
 Calculate the PValues:
 Press OK.

 You’ll obtain two Pvalues:
 Onetail value: 0.00059568
 Twotail value: 0.0011913
 Note that the onetail Pvalue is half of the twotail Pvalue. The former considers only one direction (increase or decrease), while the latter considers both directions.
 You’ll obtain two Pvalues:
 Interpretation:
 For an Alpha value of 0.05, the Pvalues are less than 0.05, indicating that we reject the null hypothesis. The data is highly significant.
Method 2 – Using the T.TEST Function
In this section, we will be using the T.TEST function to determine the P values for tails 1 and 2.
 Calculate PValue for Tail 1 (One Direction):
 In cell F5, enter the following formula:
Here,
 C4:C11: Predicted sales range
 D4:D11: Actual sales range
 1: Tail value (onetail test)
 Last 1: Paired type
 Result for Tail 1:
 The Pvalue for tail 1 is 0.00059568.
 Calculate PValue for Tail 2 (Both Directions):
 In cell F6, enter this formula:
=T.TEST(C4:C11,D4:D11,2,1)
Here,
 C4:C11: Predicted sales range
 D4:D11: Actual sales range
 2: Tail value (twotail test)
 Last 1: Paired type
Method 3 – Using CORREL and T.DIST.2T Functions
In this method, we’ll determine the Pvalue for correlation using the CORREL and T.DIST.2T functions. Follow these steps:
 Create Columns:
 Set up columns with the following headers: Total Item, Correl. Factor, t Value, and P value.
 Enter the total number of items (which is 8) in the appropriate cell.
 Calculate the Correlation Factor (Correl.Factor):
 In cell C14, enter the formula:
=CORREL(C4:C11,D4:D11)


 Here, C4:C11 represents the range of predicted sales, and D4:D11 represents the range of actual sales.

 Determine the t Value:
 In cell D14, enter the formula:
=(C14*SQRT(B142))/SQRT(1C14*C14)
Here,
 C14 is the correlation factor, and B14 is the total number of products.
 Calculate intermediate values:
 SQRT(B142) gives the square root of 6 (approximately 2.4494897).
 C14*SQRT(B142) results in approximately 1.10820197.
 1C14*C14 evaluates to approximately 0.79531473.
 SQRT(1C14*C14) returns approximately 0.891804199.
 The final t value is approximately 1.242651665.
 Compute the PValue for Correlation:
 Use the following function:
=T.DIST.2T(D14,B142)
Here,
 D14 represents the t value.
 B142 (or 82, which is 6) is the degrees of freedom.
 T.DIST.2T returns the Pvalue for correlation with a twotailed distribution.
Read More: How to Do Multiple Regression Analysis in Excel
Things to Remember
 Alpha Values:
 Commonly used significance levels are 0.05 and 0.01.
 Hypotheses:
 The null hypothesis assumes no difference between the two data sets.
 The alternative hypothesis considers a difference between the data sets.
 Interpretation:
 P < 0.05: Highly significant data
 P = 0.05: Significant data
 P = 0.050.1: Marginally significant data
 P > 0.1: Insignificant data
Practice Section
To practice we have provided a Practice section in a sheet named Practice.
Download Workbook
You can download the practice workbook from here:
Related Articles
 How to Do Simple Linear Regression in Excel
 How to Interpret Regression Results in Excel
 How to Interpret Multiple Regression Results in Excel
 How to Do Logistic Regression in Excel
 How to Plot Least Squares Regression Line in Excel
 How to Do Linear Regression in Excel
 Multiple Linear Regression on Excel Data Sets
<< Go Back to Regression Analysis in Excel  Excel for Statistics  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi, you present 3 methods to calculate pvalues for the same data set. Supposedly, results should be independent of the method chosen. So an explanation for the difference obtained in one of the methods is needed. Thanks.
Hi Jose A. Valiente, thanks for your valuable suggestion. Actually, the third method is quite different from the first 2 methods as this method determines the P value for the correlation of the two sets of values using a correlation factor.