Likelihood of a future event count within a range, from a long-term event frequency: in Excel

What to read first: About the Clear Lines Excel implementation Likelihood of a future event count within a range, from long-term event frequency

Risk specialists Version 1.0 Beta

How to know the likelihood of any number of risk events in future, when you have limited history.

Using Excel you can get the probability of exactly one event in a year, given an average frequency of 0.2 per year (one every 5 years), as:

=POISSON.DIST(1,0.2,FALSE)

The answer is 0.16375, or about 16%. The FALSE parameter on the end means that you want the probability of exactly one occurrence, not the cumulative probability of one or fewer occurrences.

You can get the probability of x or fewer occurrences using the cumulative Poisson distribution. In Excel you just flip the third parameter to TRUE, so that the probability of one or fewer events with a long-term average of 0.2 per year is:

=POISSON.DIST(1,0.2,TRUE)

The answer is 0.98248 or about 98.2%. The difference is the probability of no events, which is 81.9%. The remaining 1.8% represents the probability of two or more events in one year.

You can use the cumulative distribution twice to get the probability of an event count within any range. Suppose we want the probability of an event count between x1 and x2 (inclusive):

=POISSON.DIST(x1-1,frequency,TRUE)

That formula gives you the probability of a count strictly less than x1, while

=POISSON.DIST(x2,frequency,TRUE)

gives you the probability of a count of x2 or fewer.

That means that the formula

=POISSON.DIST(x2,frequency,TRUE)-POISSON.DIST(x1-1,frequency,TRUE)

gives you the probability of an event count between x1 and x2, inclusive of x1 and x2.

For example, if the long-term frequency is 3.5 (average 7 events per two time units), the probability of an event count of 5, 6 or 7 in one time unit is

=POISSON.DIST(7,3.5,TRUE)-POISSON.DIST(5-1,3.5,TRUE)

which calculates to 24.8%.

If you want the probability of 5 or more events, the formula is

=1-POISSON.DIST(5-1,3.5,TRUE)

which calculates to 27.5%.

Map of the series

Likelihood of a future event…

Theory

How to turn an event frequency into likelihood

count within a range, from a long-term event frequency

count within a range, from a history of events

size total within a range

Excel

About the Excel implementation

…in Excel.
This article

…in Excel.

…in Excel.

Download the complete Clear Lines Excel Workbook (17 MB)

Main article on repeating risk events and likelihood


Next article for risk specialists

Likelihood of a future event count within a range, from a history of events

First stage: the likelihood distribution Second stage: trials Comparing the trial distributions with the simple Poisson distribution Third and final stage: likelihoods for ranges Conclusions you may draw Count ranges and risk decisions

Risk specialists Version 1.0 Beta

Parent articles

Likelihood of a future event count within a range, from long-term event frequency

Risk specialists Version 1.0 Beta

Index to the topic Risk in work unit business planning

Leave a Reply

Your email address will not be published. Required fields are marked *