Inverse Poisson
Sometimes you need to work out how many failures there will be based on a confidence interval. This is called the Inverse Poisson or the Poisson Percent Point or the Poisson Quantile.
Excel doesn't have an inverse Poisson function for some reason.
Luckily someone wrote a Lambda function on Stack Overflow https://stackoverflow.com/questions/55134544/inverse-cdf-of-poisson-dist-in-excel that iteratively calculates the inverse Poisson function. Lambda functions are cool because they allow you to write a function without writing a macro, meaning it is a lot more portable and will work without VBA. It makes use of a recursive binary search / bisection method.
I made some minor modifications so that:
It works for mean values less than one
It returns #NUM! when the probability is 100%
It returns 0 when the upper test is less than 1
It returns 0 when the probability is 0
Disclaimer: use of this function is at your own risk - at extreme values you should double-check against the Poisson.dist function.
Installation
To install this formula:
Define lambda functions using Name Manager as shown below.
Then to use the function, simply do:
=POISSON_INV(L,p)
where:
L = λ lamda, or μ mean
p = Probability (0 to 100%)
Name Manager Functions
Bound_Helper, defined as:
=LAMBDA(L,p,x_upper,
IF(POISSON.DIST(x_upper,L,1)<p,Bound_Helper(L,p,2*x_upper),x_upper)
)
Inverse_Helper, defined as:
=LAMBDA(L,p,x_low,x_mid,x_high,p_low,p_mid,p_high,
LET(
check_low, p_mid <= p,
check_high, p_mid >= p,
check_prior, POISSON.DIST(x_mid-0.0001, L, 1) <= p,
check_done, AND(check_high, check_prior),
new_x_low, IFS(check_done, x_mid, check_low, x_mid, check_high, x_low),
new_x_mid, IFS(
check_done,
x_mid,
check_low,
AVERAGE(x_mid, x_high),
check_high,
AVERAGE(x_low, x_mid)
),
new_x_high, IFS(check_done, x_mid, check_low, x_high, check_high, x_mid),
new_p_low, IFS(check_done, p_mid, check_low, p_mid, check_high, p_low),
new_p_mid, IFS(
check_done,
p_mid,
check_low,
POISSON.DIST(new_x_mid, L, 1),
check_high,
POISSON.DIST(new_x_mid, L, 1)
),
new_p_high, IFS(check_done, p_mid, check_low, p_high, check_high, p_mid),
IF(
check_done,
FLOOR.MATH(new_x_mid),
IF(
new_x_high < 1,
0,
Inverse_Helper(L, p, new_x_low, new_x_mid, new_x_high, new_p_low, new_p_mid, new_p_high)
)
)
)
)
Poisson_Inv, defined as:
=LAMBDA(L,p,
MAP(
p,
LAMBDA(arg,
LET(
x_high, Bound_Helper(L, arg, 1),
IFS(
p = 1,
#NUM!,
p = 0,
0,
TRUE,
Inverse_Helper(
L,
arg,
0,
L,
x_high,
POISSON.DIST(0, L, 1),
POISSON.DIST(L, L, 1),
POISSON.DIST(x_high, L, 1)
)
)
)
)
)
)
Note 1: Google Sheets doesn't support Lambda functions, so you'll have to download this Excel file and use Microsoft Excel to see this function in action.
Note 2: While the Real Statistics website (https://real-statistics.com/binomial-and-related-distributions/poisson-distribution/) has a similar poisson_inv function, it seems to underestimate the Poisson quantile for small means (< 0.3) and high probabilities (> 99.8%).
Note 3: I have validated the formula on this page for some of the edge cases where the Real Statistics formula differs, by using direct calculations using the built-in poisson.dist Excel function, and also by using https://www.123calculus.com/en/inverse-poisson-distribution-page-1-50-247.html, and also by using GNU Octave (https://octave-online.net/) using the poissinv(x, lambda) command.
For more information about the Poisson function and the inverse Poisson function along with some graphs, please see:
