How to use the PROB function in Excel Spreadsheets?
Tags: tousetheprobfunction, excel, excelfunctions
Published: 2021-08-24 02:18:48

---
title: How to use the PROB function in Excel Spreadsheets?
published: true
date: 2021-08-18 19:40:41 UTC
tags: ToUseThePROBFunction,Excel,ExcelFunctions
canonical_url: https://geekexcel.com/how-to-use-the-prob-function-in-excel-spreadsheets/
---

 **PROB function** returns the **probability for an event or range of events,** providing a **lower and upper limit** of an event. **X\_range and prob\_range** are different events and their probabilities. In this article, we are going to see how to  **use the PROB Function In Excel Spreadsheets.**  Let’s get into this article!! Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

## Probability

**Probability** means what are the **probable chances of an event** occurring in a situation. The mathematical formulation to calculate the probability is given by :

```
Probability = number of occurrence of an event / total number of occurrences
```

## PROB Function syntax

```
=PROB(x_range, prob_range, lower_limit, [upper_limit])
```

**Syntax Explanation:**

- **x\_range** : different **events** given as **array**.
- **prob\_range** : different respective **probabilities** given as array.
- **lower\_limit** : probability of an event or **lower limit** of an event.
- **upper\_limit** : [optional] only needed when **range is given as argument**.

## Example1

- Firstly, you have to create example of rolling a regular to dice having **1 to 6  numbers**.

![](https://geekexcel.com/wp-content/uploads/2021/08/Sample-data-11.png)<figcaption>Sample data</figcaption>

- The probability of getting one equal to is given below.

```
=1/6
```

![](https://geekexcel.com/wp-content/uploads/2021/08/Sample-data-10.png)<figcaption>Use the probability formula</figcaption>

- Then, you need to calculate the probability of getting **1 or 2 or 3**  **on rolling the same die**. We use the PROB function.
- Now, you have to use the following formula given below.

```
=PROB(A2:A7, B2:B7, 1, 3)
```

![](https://geekexcel.com/wp-content/uploads/2021/08/PROB-formula.png)<figcaption>PROB Formula</figcaption>

**The explanation for the formula:**

- B4:B9 is the event range
- C4:C9 is the respective probabilities
- 1 the lower limit of event
- 3 upper limits of the event

## Example2

- You need to create the example for the **roll of two dice**.

![](https://geekexcel.com/wp-content/uploads/2021/08/Sum-of-two-dice.png)<figcaption>Sum of two dice</figcaption>

- Then, you have to calculate the **probability of the sum of numbers** occurring on the roll.
- Now, you need to use the **formula** to count the **number of 2s** in the data.

```
=COUNTIF(data,B11)/COUNT(data)
```

![](https://geekexcel.com/wp-content/uploads/2021/08/count-number-of-2s.png)<figcaption>Count the number of 2s</figcaption>

- After that, you have to calculate the **probability of getting a sum less than 7.**
- You need to use the formula given below.

```
=PROB(B11:B21, C11:C21, 2, 6)
```

Explanation :

- B11:B21 is the event range
- C11:C21 is the respective probabilities
- 2 the lower limit of event
- 6 upper limits of the event (less than 7)

![](https://geekexcel.com/wp-content/uploads/2021/08/less-than-7.png)<figcaption>Less than 7</figcaption>

- After that, you have to calculate the **probability of getting exact 7.**
- You need to use the formula given below.

```
=PROB(B11:B21, C11:C21, 7)
```

![](https://geekexcel.com/wp-content/uploads/2021/08/getting-exact-7.png)<figcaption>Getting exact 7</figcaption>

- After that, you have to calculate the **probability of getting a sum greater than 7.**
- You need to use the **formula** given below.

```
=PROB(C11:C21, D11:D21, 8, 12)
```

Explanation :

- C11:C21 is the event range
- D11:D21 is the respective probabilities
- 8 the lower limit of event (greater than 7)
- 12 upper limit of the event

![](https://geekexcel.com/wp-content/uploads/2021/08/greater-than-7.png)<figcaption>greater than 7</figcaption>

**NOTE:**

- The **PROB** function only works with **numbers**.
- Then, the **function** returns **#NUM! Error** if
  - Any **probability value** in **prob\_range is < 0 or > 1**.
  - If the **sum of all probability** in **prob\_range is not equal to 1**.
  - If any of the cell in the **x\_range or prob\_range** is blank.
- The function returns **#VALUE! Error** if any any **value is non-numeric**.
- The function returns **#N/A error** if the supplied **x\_range and prob\_range arrays** are of different lengths (i.e. contain different numbers of data points).

## Bottom-Line

Here, we have described the simple formulas to **use the PROB Function In Excel Spreadsheets**. Let me know if you have any **doubts** regarding this article or any other Excel/VBA topic.

Thank you so much for visiting  **[Geek Excel](https://geekexcel.com/)!! **If you want to learn more helpful formulas, check out [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Read Next:**

- **[Excel Formulas to Find the Next Scheduled Event Date from Today!!](https://geekexcel.com/excel-formulas-to-find-the-next-scheduled-event-date-from-today/)**
- **[Formulas to Limit a Percentage Between 0 and 100!!](https://geekexcel.com/excel-formulas-to-limit-a-percentage-between-0-and-100/)**
- **[Excel Formulas to Generate Random Number Weighted Probability!!](https://geekexcel.com/excel-formulas-to-generate-random-number-weighted-probability/)**
- **[How to use the CONFIDENCE.NORM function in Excel office 365?](https://geekexcel.com/how-to-use-the-confidence-norm-function-in-excel-office-365/)**
- **[How to use the Z.TEST function in Excel Office 365?](https://geekexcel.com/how-to-use-the-z-test-function-in-excel-office-365/)**