Reverse Poisson?

[largeselection]

Not sure if anyone can help with this. Basically I'm trying to find out how to apply the reverse of the Poisson function in excel. So as of now I have poisson(x value, mean, true-cumulative) and that lets me get the probability for that occurence. Basically I want to know how I can get the minimum/maximum x value based on a given probability.

So if I have a list of data (700 rows) and I want to find out what the minimum starting value should be given a desired average and the fact that I want the lowest value to be at the 0.05% probability. So 0.05% = (x, 35, True) solve for x.

I know I can prob do this with solver, but I am trying to figure out a way to do this formulaicly without having to use the solver (as I may have to use this many times)

Thanks for any insight/links/help as always!

 

[Animalich]

A further mod to accommodate large means:

Function PoissonInv(Prob As Double, Mean As Double) As Variant
  ' shg 2011, 2012, 2014, 2015-0415

  ' For a Poisson process with mean Mean, returns a three-element array:
  '   o The smallest integer N such that POISSON(N, Mean, True) >= Prob
  '   o The CDF for N-1 (which is < Prob)
  '   o The CDF for N (which is >= Prob)

  ' E.g., POISSON(5, 10, TRUE) returns 0.067085962
  ' PoissonInv(0.067085962, 10) returns 5

  ' Returns a descriptive error if Prob <= 0 or Prob >= 1
  ' If Mean >= 100, then uses a normal approximation

  Dim N             As Long     ' number of events
  Dim CDF           As Double   ' cumulative distribution function at N
  Dim CDFOld        As Double   ' CDF at N-1

  ' These two variables are used to simplify the probability mass
  ' function summation CDF = CDF + Exp(-Mean) * Mean ^ N / N!
  
  Dim dExpMean      As Double   ' =Exp(-Mean)
  Dim dK            As Double   ' incremental power & factorial

  If Prob <= 0 Or Prob >= 1 Then
    PoissonInv = "Prob ]0,1["

  ElseIf Mean < 100 Then
    dExpMean = Exp(-Mean)
    dK = 1#
    CDF = dExpMean

    Do While CDF < Prob - 0.000000000000001
      CDFOld = CDF
      N = N + 1
      dK = dK * Mean / N
      CDF = CDF + dExpMean * dK
    Loop

    PoissonInv = Array(N, CDFOld, CDF)

  Else
    ' Plan B, for large means; approximate the Poisson as a normal distribution
    ' http://en.wikipedia.org/wiki/Continuity_correction#Poisson
    Dim iInv        As Long

    With WorksheetFunction
      iInv = .Ceiling(.Norm_Inv(Prob, Mean, Sqr(Mean)) - 0.5, 1)
      PoissonInv = Array(iInv, _
                         .Norm_Dist(iInv - 0.5, Mean, Sqr(Mean), True), _
                         .Norm_Dist(iInv + 0.5, Mean, Sqr(Mean), True))
    End With
  End If
End Function

Is there any way to apply this if you want the inverse of the poisson formula when using false in the excel formula?