Nazmul Hossain Shovon
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.
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
- 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)
- 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
- How to Calculate Cumulative Probability in Excel (with Easy Steps)
- How to Find Standard Deviation of Probability Distribution in Excel
- Apply Weighted Probability in Excel (3 Useful Methods)
- How to Calculate Binomial Probability in Excel (with Easy Steps)
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.
- 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)
- 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
- 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.
