How to find the P value in Excel

You can watch a video tutorial here.

The P-value is used in hypothesis testing to help determine whether to accept or reject the null hypothesis. In Excel, we can compute the P-value using the Data Analysis tool or by using the T.TEST function. The syntax of the T.TEST function is:

T.TEST(array1,array2,tails,type)

• array1: the range of the first data set.
• array2: the range of the second data set.
• Tails: 1 for the one-tailed distribution and 2 for the two-tailed distribution.
• Type: the kind of t-Test to perform.

In this example, we look at whether the kilometers driven affect the selling price of previously-owned cars. 

Option 1 – Use the T.TEST() function

Step 1 – Create the formula

• In the destination, type the formula, using cell references:

=T.TEST(range of Selling_Price, range of KM_driven,1,1)

• Press Enter
• The P-value is displayed
• Format for percentage by clicking the Percent Style button (%) on the ribbon
• Increase the decimal places by clicking the Increase decimal button on the ribbon

Options 2 – Use the Data Analysis tool

Note: If the Data Analysis button is present under Data > Analyze,  then skip steps 1 to 3

Step 1 – Open the Excel Options window

• Go to File > Options

Step 2 – Manage the Add-ins

• Go to Add-ins
• Select Excel Add-ins from the Manage drop-down
• Click Go

Step 3 – Load the Analysis ToolPak add-in

• Select Analysis ToolPak
• Click OK

Step 4 – Open the t-test: Paired Two Sample for Means window

• Go to Data > Analyze
• Click on the Data Analysis button
• In the window, select t-test: Paired Two Sample for Means
• Click OK

Step 5 – Set the parameters

• Define the input ranges:
  • Input Y Range = the range of the first variable i.e. the ‘Selling Price’
  • Input X Range = and range of the second variable i.e. ‘KM_driven’ 
• Define the output range i.e where you want the result to be displayed
• Click OK

Step 6 – Check the result

• The results of the test are displayed, including the P-value
• Format the P-value for percentage by clicking the Percent Style button (%) on the ribbon
• Increase the decimal places of the P-value by clicking the Increase decimal button on the ribbon