Excel's primary function is running calculations for you and analyzing data sets in many different ways. One of the most useful tools is calculating a p-value, or a probability value.
Jump To:
- How to find the p-value in Excel
- Method 1. Find the p-value using T-Test
- Method 2. Find the p-value using Data Analysis
-
Things you may want to know about the p-value
Probability is a key concept in statistical hypothesis testing and may help you in many projects. Finances, physics, economics, and other fields all benefit from calculating p-values, and Microsoft's software makes it easy to do just that. Forget complex calculations and let Excel handle the math.
In this article, you can learn how to calculate probability values or p-values in different versions of Excel.
How to find the p-value in Excel
There are currently 2 ways of finding the probability value in Excel: the T-Test function and the Data Analysis tool. We’ve set up an example below for both approaches. We recommend you try the one more suitable for your project and skill level.
Disclaimer: For this article, we’ll be using the most recent Microsoft Excel 2019. Some steps may be different in earlier or later versions of the software.
Let’s get right into it.
Method 1. Find the p-value using T-Test
The guide below gives step-by-step instructions for using the T-Test function in order to calculate the p-value.
- Open the Excel document you want to work with or create a new one. Make sure there’s already data in the workbook before you proceed.
-
Select any cell outside of your data set. Input the following formula: =T.Test(
-
Type in the first argument. In our example, this would be the entire Before (kg) column. The formula should change accordingly, automatically inputting the cells.
-
Next, type a comma (,) and select the second argument. In the example we set up, this would be the After (kg) column, further completing the function.
-
Type another comma (,) after the second argument. Here, double-click on the One-tailed distribution from the options.
-
Type another comma (,) and double-click on Paired.
-
Now, all elements the function needs have been selected. Use the ) symbol to close the bracket, and then press Enter.
- The selected cell will display the p-value immediately.
So, what does this mean? In this example, the p-value is low. This means that the research can safely conclude that the test didn’t result in significant weight-loss. This doesn’t mean the null hypothesis is correct, only that it hasn’t been disproven.
Method 2. Find the p-value using Data Analysis
The Data Analysis tool allows you to play with your data in a number of different ways. This includes finding the p-value of your data set. Follow the instructions below to learn how.
To keep things simple, we’ll be using the same data set as in the previous method.
- Open the Excel document you want to work with or create a new one. Make sure there’s already data in the workbook before you proceed.
-
Switch to the Data tab in the ribbon header interface, and then click on Data Analysis from the Analysis group.
-
If you don’t have this option, navigate to File → Options → Add-ins and click on the Go button. Select Analysis ToolPak and click OK. The option should appear on your ribbon now.
-
In the pop-up window, scroll down and select t-Test: Paired Two Sample for Means, and then click OK.
-
Enter your arguments. Make sure to place a “$” symbol before each digit, excluding the “:” symbol between two cells. Your setup should look similar to this example:
- You should leave the Alpha text box on the default value. If you changed it, change it back to 0.05.
-
Select the Output Range option and type the cell you want to use in the field. For example, if you want to use the A9 cell to display the p-value, type in $A$9.
-
The final table will contain a number of calculations, and the p-value result. You can use this table to get interesting facts about your data.
Things you may want to know about the p-value
Calculating and finding p-values is a complex process. Probability is one of the hardest fields to tackle, even for experienced and educated individuals. Here are some things to note when working with p-values in Excel:
- The data in your table is important if the p-value is 0.05 (5 percent). However, the data you have is more relevant if it is less than 0.05 (5%).
- If the p-value is more than 0.1 (10%), the data in your table is insignificant.
- You may change the alpha value to a different number; however, most people tend to fluctuate between 0.05 (5%) and 0.10 (10%).
- Choose two-tailed testing instead of one-tailed testing if it’s better for your hypothesis.
- P-values can’t identify data variables. If a correlation is identified, the p-value calculation isn’t able to detect the cause(s) behind it.
Final thoughts
If you need any further help with Excel, don’t hesitate to reach out to our customer service team, available 24/7 to assist you. Return to us for more informative articles all related to productivity and modern-day technology!
Would you like to receive promotions, deals, and discounts to get our products for the best price? Don’t forget to subscribe to our newsletter by entering your email address below! Receive the latest technology news in your inbox and be the first to read our tips to become more productive.
You may also like
» What is Flash Fill in Excel? How do I use it?
» Excel for mac
» How to Calculate Break-Even Analysis in Excel