How to Calculate Probability Using Excel 2022 Simple Steps you Can Take
– How to Calculate Probability Using Excel –
Probability is the likelihood that an event will take place. It is measured by comparing the desired event with the total number of outcomes. 
The formula to calculate probability can be rather complex. However, Microsoft Excel has a built-in formula to calculate probability.
Probability is defined as the likelihood for which an event is probable, or likely to happen.
It is measured through the ratio of favorable events to the total number of causes. Excel has a built-in formula to calculate probability through the PROB function.
How to Calculate Probability in Excel
Go to Start>Programs>Microsoft Office>Microsoft Excel. If Excel has been used recently, simply go to Start>Microsoft Excel.
Create two columns, one entitled “Numeric grades” and the other “Probability of getting each grade.” List the grades from 50, 60, 70, 80, 90, and 100 in cells A2 to A7.
List the probabilities associated with each grade from cells B2 to B7. List the numbers as follows: 0.05, 0.1, 0.4, 0.3, 0.1 and 0.05.
Enter “=Prob(A2:A7,B2:B7,70,100)”. This formula for probability isolates the numeric range of numbers (A2: A7), the probability of getting each grade (B2: B7), the lower range for which the probability is needed (70), and the upper range for which the probability is needed (100).
In short, the formula answers the question: What are the chances of someone getting a grade between 70 and 100?
Click on the “%” icon to convert the answer (0.85) to a percentage. The resulting answer is 85%. There is an 85% chance that the grade will be between 70 and 100.
Syntax of PROB
=PROB(range, prob_range, [lower_limit], [upper_limit])
- range – the range of numeric values containing our data
- prob_range – the range of probabilities for each corresponding value in our range
- lower_limit – optional; the lower limit of the values for which we want to calculate the probability
- upper_limit – optional; the upper limit of the values for which we want to calculate the probability
Read Also:
- How to Deselect in Photoshop
- Microsoft Global Customer Service Support Phone Numbers
- Peso Sign in Microsoft Word
- How to Get Roman Numerals in Microsoft Word
How to Calculate Probability?
In order to obtain reliable probability results, we should be able to prepare the data prior to calculation.
Prepare Our Data
The below table contains a list of Product Sales and their corresponding probabilities. The sum of all probabilities should be equal to 100. Otherwise, the PROB function returns the #NUM! error.
We want to calculate the probability that product sales are between 50 and 80.
Calculating Probability
To perform the calculation, we enter this formula in cell C11
=PROB(B4:B7,C4:C7,C9,C10)
Where B4: B7 is the range containing the values for product sales, C4: C7 contains the probabilities for each sales quantity, C9 is the lower limit of 50 and C10 is the upper limit of 80.
As a result, the probability in cell C11 is 0.68 or 68%, which is the probability that product sales is between 50 and 80.
Calculate the probability without Upper Limit
If there is no upper limit, the PROB function returns the probability of being equal to the lower limit only.
Example:
If we omitted the upper limit in our formula, the result in cell C11 is 0.50 or 50%, which is also the probability of product sales being equal to 50.
How to Calculate Normal Distribution Probability in Excel
While it’s possible to look up probabilities for a normal distribution using the z-table, it’s actually much easier to calculate probabilities in Excel for a couple of reasons.
First, there’s no looking at a table; the NORMDIST function does the hard work for you. Second, Excel does the intermediate calculations for you.
Most calculation errors happen in an intermediate step (such as calculating the z-score to look up) rather than the actual z-score itself.
Excel can handle three types of probability calculations: more than, less than, and in between. These instructions work for Excel 2007 and Excel 2010.
Sample Question
Suppose that a contaminant in samples from a city’s water supply has a mean of 500 ppm and a standard deviation of 100 ppm. What is the probability that bacteria in a randomly selected water sample will be: 1/ less than 600 ppm 2/More than 600 ppm 3/between 400 and 600 ppm?
Calculate Normal Distribution Probability in Excel: Less than
Step 1: Click an empty cell.
Step 2: Click “Insert Formula”.
3rd Step: Type “Normdist” into the search box and then click “Go.”
Step 4: Select “NORMIST” from the list and then click “OK” to open the Function Arguments window.
Step 5: Enter your daDta into the box. For this example, type “600” in the X box, “500” in the Mean box, “100” in the Standard Deviation box and “true” in the cumulative box..
6th Step: Click “OK.”. This returns 0.84134474 in the cell you clicked in Step 1, which is the probability of getting under 600 ppm.
Calculate Normal Distribution Probability in Excel: More than
Step 7: Subtract your answer from Step 7 (above) from 1:
1-0.84134474= 0.158653.
Calculate Normal Distribution Probability in Excel: Between
Step 8: Repeat Steps 1 through 8 for the second value, which for this example is 400. The probability is 0.15865526.
Step 9: Subtract the larger probability from the smaller probability. In this example:
0.84134474 – 0.15865526 = 0.68269
Few Things to Remember About the PROB Function
- #NUM! error – Occurs if either:
- Any value in the given prob_range is less than 0 or greater than 1; or
- The values in the given prob_range do not add up to 1.
- N/A! error – Occurs if the given x_range and prob_range arrays are of different lengths (i.e., contain different numbers of data points).
- If we omit the upper_limit, PROB returns the probability of being equal to the lower_limit.
- The PROB function was introduced in MS Excel 2007 and hence is unavailable in earlier versions.
There is never a time that isn’t right to help. If this information was useful to you, do well to share it with friends and loved ones. It’s your turn to help other people. You can share this article on your favorite social media handle.
