I'm a IT manager that deals with delays from various departments in a purchase process. In a given phase we have 25 handovers and thus 25 response times. So many variable times (and without SLA) creates serious uncertainty about the final date, that reflects in IT risk and cybersecurity risk. I collected the timestamps of these handovers from 5 previous purchases to model expected date in which a purchase will reach the end step (i.e. signed contract). I want to know optimist, mean and pessimist estimates. So, I computed the following statistics: Minimum > Mean minus Standard Deviation > Mean > Mean plus Standard Deviation > Maximum

Then I found a problem. Let's compute for the following sample (values are days): 0.2, 1.0, 0.3, 0.0, 0.1

We get:

  • Minimum: 0.0
  • Mean minus Standard Deviation: -0.1
  • Mean: 0.3
  • Mean plus Standard Deviation: 0.7
  • Maximum: 1.0

That -0.1 was unexpected (by me). I was expecting some value between minimum (0.0) and mean (0.3) that could tell me what a rapid response is, based on historical values. I realized that I was presuming a normal distribution, that's not the case.

So I tried to use 25th and 75th percentiles, instead of standard deviations. And then hit another problem. Let's compute for the following sample: 2.0, 2.0, 2.0, 2.0, 3.0

That is: tipically it takes 2 days, but on one occasion it took 3. Something between [2,3] should describe the reality. But my metrics become:

  • Minimum: 2.0
  • 25th perc.: 2.0
  • Mean: 2.2
  • 75th perc.: 2.0
  • Maximum: 3.0

Wait. I have a 75th percentile (2.0) that's lower than mean (2.2). Statistically computed right, but I can't use 75th percentile to model a case worst than mean. I'm figuring something wrong. I was expecting that a percentile could be a value not present on sample, as if would have continuous values... some interpolation.

So, my mind is in interpolating some normal distribution, but my lacking statistics skills are not leading me to a solution. Can you please help me to model this?

I'm using Excel. Some solution feasible in excel would be great.

  • $\begingroup$ What is the problem with the 75th percentile being smaller than the mean? This happens all the time. Indeed, the mean (and probably the SD also) look irrelevant to your problem. Wouldn't the pessimistic and optimistic estimates be reasonable guesses of some lower and upper percentile of the distribution, respectively? What we most need to resolve your problem is a statement from you about what you mean by "pessimistic" and "optimistic." One possible example would be that you want to be very confident that future values have less than, say, a 5% chance of exceeding your high estimate. $\endgroup$
    – whuber
    Commented Dec 16, 2021 at 20:12
  • $\begingroup$ In software engineering, where people who maintain APIs that serve requests, people often measure the median / 90th percentile / 95th percentile / 99th percentile (in some cases with more 9s behind) of the response time, and are obsessed in getting these numbers down in the face of all kind of risks imposed by external factors. Would that be a reasonable analogue to your case? While it is harder (but not impossible with excel) with 5 data points, given you mention IT risk and cybersecurity risk, fitting a right-skewed and/or heavy-tailed distribution might be apt. $\endgroup$
    – B.Liu
    Commented Dec 16, 2021 at 20:37
  • $\begingroup$ @whuber I don’t know how to adequately frame my problem in mathematically formal way. What I’m trying to spot is: optimistic ia an idea of how fast past activities gone, without being overly optimistic, that is, minimum of all time. This means: if everyone involved pushes hard, we can finish this fast, because we did it already in the past. Pessimistic is the opposite: not the overly pessimistic that is the maximum of all times, but if everyone don’t mind too much, what’s should be the projected date. $\endgroup$
    – Filipe
    Commented Dec 21, 2021 at 2:48
  • $\begingroup$ @whuber I’m now adopting optimistic the average of 20th, 23th, 26th, 29th and 32th percentiles. Pessimistic the average of 68th, 71th, 74th, 77th and 80th percentiles. I average several percentiles to avoid that with a few samples some low and high percentiles can get too close to minimum and maximums. Totally arbitrary, don’t know if it holds agains any scientific rational. Can you provide opinions on that? $\endgroup$
    – Filipe
    Commented Dec 21, 2021 at 2:53
  • $\begingroup$ I can't see any rationale to be doing that instead of using the 25th and 75th percentiles. It doesn't seem to accomplish anything. $\endgroup$
    – whuber
    Commented Dec 21, 2021 at 14:37

1 Answer 1


You do not give much information about these waiting times, so this answer has to be regarded as highly speculative.

Often waiting times are well-modeled as exponential. Your small sample $0.2, 1.0, 0.3, 0.0, 0.1$ has high outlier $1.0$ which may suggest such a right skewed distribution. A sample of size five is hardly enough for reliable speculation about your distribution of waiting times.

If $\bar X$ is the mean of an exponential population of size $n,$ one has $\frac{\bar X}{\mu} \sim \mathsf{Gamma}(\mathrm{shape}=n,\mathrm{rate}=n).$ This relationship can be 'pivoted' to give a 95% CI for $\mu$ of the form $\left(\frac{\bar X}{U}, \frac{\bar X}{L}\right),$ where $L$ and $U$ cut area 0.025 from the lower and upper tails of $\mathsf{Gamma}(n,n),$ respectively.

So if you have $n = 20$ exponential observations with $\bar X = 0.35,$ then a 95% CI for $\mu$ is $(0.24, 0.57).$ [Computations in R.]

[1] 0.2359218 0.5729946

Taking the worst-case scenario that the mean is as large as $\mu = 0.57,$ one might speculate that 99% of the time the waiting time would be less than $2.624.$

qexp(.99, 1/0.57)  # mean 0.57 implies rate 1/0.57
[1] 2.624947

Of course, this is based on only 20 observations, the assumption that future waiting times will imitate past ones, and the assumption that waiting times are exponential.

By contrast, if you have a substantial amount of data on relevant past waiting times, then you have more information and fewer assumptions. You might use the 99th percentile of that data for a 'pessimistic' prediction of the next waiting time, as shown below.

y = rexp(1000, 1/0.35)  # fictitious exponential data
                          for illustration
     Min.   1st Qu.    Median      Mean   3rd Qu.      Max. 
0.0002822 0.0990812 0.2418578 0.3520501 0.4953001 2.3407786 

quantile(y, .99)

Based on my fictitious data, only ten times in 1000 such situations was the waiting time more than $1.536$ and the waiting time was never more than $2.35.$

sum(y > 1.536)
[1] 10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.