How to Calculate Probability of Exceedance in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to show you 3 easy methods to calculate probability exceedance in excel. You can use these methods even in large datasets to find out the exceedance value for a certain period. Throughout this tutorial, you will also learn some important excel tools and techniques which will be very useful in any excel related task.


Download Practice Workbook

You can download the practice workbook from here.


What Is Probability of Exceedance?

The probability of exceedance, as the name suggests, gives the indication of how likely a certain value is to exceed the regular limits. We can estimate this probability using the following formula:

Probability of Exceedance = 1- ( 1-p)^n

Where,

p  = probability in the current period

n = the time period to consider


3 Easy Methods to Calculate the Probability of Exceedance in Excel

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 5 rows and 3 columns. For all the datasets, we have 3 unique input values which are p, n, and 1-p. Although we may vary the number of columns later on if that is needed.

how to calculate probability of exceedance in excel


1. Calculating Exceedance by Formula

In this first method, we will use simple excel operators to calculate the exceedance probability value. Follow the steps below to do this.

Steps:

  • First, go to cell C6 and enter the following formula:
=1-C4

formula to calculate probability of exceedance in excel

  • Next, press Enter.
  • Furthermore, enter the below formula in cell C7:
=(1-C4)^C5

  • Again, press the Enter key to confirm.
  • Then, put this formula inside cell C8:
=1-C7

  • Finally, press Enter for the last time and this should calculate the probability exceedance.

Read More: How to Calculate Probability in Excel (3 Practical Examples)


2. Using POWER Function

The POWER function in excel is an alternate option to the ^ operator. It raises a number to some power and returns the value. Let us see how we can use this function to calculate the probability of exceedance.

Steps:

  • To begin with, double-click on cell C7 and enter the below formula:
=POWER(C6,C5)

power function to calculate probability of exceedance in excel

  • Next, press the Enter key and you should get a value of 25.
  • Now, double-click on cell C8 and insert the formula below as before:
=1-C7

  • Lastly, press the Enter key and consequently, this will find the probability value inside cell C8.

Read More: Use Normal Probability Distribution in Excel (3 Cases)


Similar Readings


3. Applying VBA Code

If you want to calculate the probability of exceedance in excel using just one click, then you should use the VBA feature. Follow the steps below to apply this.

Steps:

  • For this method, go to the Developer tab and select Visual Basic.

vba code to calculate probability of exceedance in excel

  • Now, select Insert in the VBA window and click on Module.

  • Next, type in the formula below in the new window:
Public Sub Exceedance()
Range("C6").Value = 1 - Range("C4")
Range("C7").Value = Application.WorksheetFunction.Power(Range("C6"), Range("C5"))
Range("C8").Value = 1 - Range("C7")
End Sub

  • Then, open the macro from the Developer tab by clicking on Macros.

  • After that, in the Macro window, select the Exceedance macro and click Run.

  • As a result, the VBA code will calculate the probability exceedance value including all the other parameters.

Read More: Exponential Probability Distribution in Excel (2 Suitable Examples)


How to Calculate Probability in Excel with Mean and Standard Deviation

Oftentimes, we need the values of mean and standard deviation to calculate probability in excel. In this method, we will see how we can achieve this using the NORMDIST function.

Steps:

  • To begin this, double-click on cell C10 and insert the formula below:
=NORMDIST(66.67,C8,C9, TRUE)

How to Calculate Probability in Excel with Mean and Standard Deviation

  • Next, press the Enter key and consequently, this will find the probability value inside cell C10.


How to Calculate Conditional Probability in Excel

In the following steps, we shall see how to find the conditional probability from a specific dataset in excel.

Steps:

  • To start this method, navigate to cell D8 and type in the following formula:
=C5/C7

How to Calculate Conditional Probability in Excel

  • Then, press Enter to confirm the formula.
  • Now, type in the below formula in cell D9:
=D5/D7

  • Finally, press Enter and immediately this will evaluate the conditional probability values.


Things to Remember

  • Use the shortcut Alt+F11 to open the VBA window as an alternative.
  • Make sure to rename the sheet in the VBA method as VBA.
  • Also, you can press Alt+F8 to open the Macro window to run it.

Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to calculate probability exceedance in excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon
Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo