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
- 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.